CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008sql-server-2012
Zeeshan Adil
Zeeshan Adil

Reputation: 2115

How to Convert Columns to Rows and Replace Column Names

I have a Table that returns me the Amount of products w.r.t to the Months in a row like:

[M02] | [M03] | [M04] | [M05] | [M06] | [M07] | [M08] | [M09] |[M10] | [M11] | [M12]
------------------------------------------------------------------------------------
120   | 65    | 75    | 587   | [78   | 5656  | 651   | 65    | 8949 | 89   | 596

now I need the result as [here I want to replace the columns into rows and change their names to the aliases of my choice] e.g M01 to January from column to row

  [Month]  | [Count] |
----------------------
  January  |     120 |
  February |     65  |

I know I can achieve this with the UnPivot operator but I'm unable to get the name of the months as January as I can't set alias inside the unpivot

I wrote this query

SELECT [Months],[Count]
  FROM [TableName]
  UnPivot(
  [Count] for Months in ([M01]
      ,[M02]
      ,[M03]
      ,[M04]
      ,[M05]
      ,[M06]
      ,[M07]
      ,[M08]
      ,[M09]
      ,[M10]
      ,[M11]
      ,[M12])
  ) as unpiv

this give me data as:

      [Month]  | [Count] |
    ----------------------
      M01      |     120 |
      M02      |     65  |

here M01, M02 etc are months respectively but I can't change their name. how can I achieve this?

Upvotes: 4

Views: 471

Answers (2)

PSK
PSK

Reputation: 17943

Approach 1 : Using CASE WHEN

You can use CASE WHEN like following to get the desired output.

SELECT    
    (CASE 
    WHEN [Months]= 'M01' then 'January'
    WHEN [Months]= 'M02' then 'February'
        --PUT Remaning conditions
    END) [Months],
    [Count]

FROM
(
SELECT [Months],[Count]
    FROM [TableName]
    UnPivot(
    [Count] for Months in ([M01]
        ,[M02]
        ,[M03]
        ,[M04]
        ,[M05]
        ,[M06]
        ,[M07]
        ,[M08]
        ,[M09]
        ,[M10]
        ,[M11]
        ,[M12])
    ) as unpiv
)T

Approach 2: INNER JOIN to mapping table

You can have a mapping table, where you map your codes with months and put a INNER JOIN like 

SELECT 
MP.MonthName,
T.[Count]
FROM
(
SELECT [Months],[Count]
  FROM [TableName]
  UnPivot(
  [Count] for Months in ([M01]
      ,[M02]
      ,[M03]
      ,[M04]
      ,[M05]
      ,[M06]
      ,[M07]
      ,[M08]
      ,[M09]
      ,[M10]
      ,[M11]
      ,[M12])
  ) as unpiv
)t
INNER JOIN [YourMappingTable] MP ON MP.Code=T.Months

Approach 3: String functions.

Another approach can be using RIGHT with PATINDEX to get the number and finally converting the number to a month name.

SELECT 
DateName( month , DateAdd( month ,  CAST(RIGHT(T.[Months],PATINDEX('%[0-9]%',T.[Months])) AS INT) , 0 ) - 1 ) as Months,
[Count]
FROM
(
SELECT [Months],[Count]
  FROM [TableName]
  UnPivot(
  [Count] for Months in ([M01]
      ,[M02]
      ,[M03]
      ,[M04]
      ,[M05]
      ,[M06]
      ,[M07]
      ,[M08]
      ,[M09]
      ,[M10]
      ,[M11]
      ,[M12])
  ) as unpiv
)t

Note: If the format is fixed, you can also try like following without using PATINDEX

DateName( month , DateAdd( month ,  CAST(RIGHT(T.[Months],2) AS INT) , 0 ) - 1 ) as Months,

Upvotes: 3

abdifatah supparka
abdifatah supparka

Reputation: 21

You cannot assign an alias inside of the UNPIVOT function so you would have to use the CASE expression.

Upvotes: 2

Related Questions