Pivoting/Rotating row to column in SQL Server T-SQL

Posted by Sudipta Bandyopadhyay on Wednesday, October 12, 2011 with No comments
This code will help to convert data organized as rows to be organized as columns.
Problem statement:
Say I have a table (MyTable):

Year
 Month
 Count
2009
12
22
2010
5
19
2010
2
1914
2010
3
51
2010
11
40
2010
12
3
2010
9
28
2010
1
138
2010
10
103

I need the output like this:


Year
 Jan
 Feb
 Mar
 Apr
 May
 Jun
 Jul
 Aug
 Sep
 Oct
 Nov
 Dec
2009
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
 NULL
22
2010
138
1914
51
 NULL
19
 NULL
 NULL
 NULL
28
103
40
3



SQL way to achieve the goal:

 
SELECT Year, 
SUM(Jan) AS Jan,
SUM(Feb) AS Feb,
SUM(Mar) AS Mar,
SUM(Apr) AS Apr,
SUM(May) AS May,
SUM(Jun) AS Jun,
SUM(Jul) AS Jul,
SUM(Aug) AS Aug,
SUM(Sep) AS Sep,
SUM(Oct) AS Oct,
SUM(Nov) AS Nov,
SUM(Dec) AS Dec
FROM (
 
SELECT Year, 
Jan = CASE MONTH WHEN 1 THEN Count end,
Feb = CASE MONTH WHEN 2 THEN Count end,
Mar = CASE MONTH WHEN 3 THEN Count end,
Apr = CASE MONTH WHEN 4 THEN Count end,
May = CASE MONTH WHEN 5 THEN Count end,
Jun = CASE MONTH WHEN 6 THEN Count end,
Jul = CASE MONTH WHEN 7 THEN Count end,
Aug = CASE MONTH WHEN 8 THEN Count end,
Sep = CASE MONTH WHEN 9 THEN Count end,
Oct = CASE MONTH WHEN 10 THEN Count end,
Nov = CASE MONTH WHEN 11 THEN Count end,
Dec = CASE MONTH WHEN 12 THEN Count end
 
FROM MyTable
) AS TempTable
GROUP BY Year
ORDER BY Year


Using Pivot in TSQL:

SELECT Year, 
[1] AS Jan,
[2] AS Feb,
[3] AS Mar,
[4] AS Apr,
[5] AS May,
[6] AS Jun,
[7] AS Jul,
[8] AS Aug,
[9] AS Sep,
[10] AS Oct,
[11] AS Nov,
[12] AS Dec
 
FROM
(
SELECT * FROM MyTable
PIVOT (SUM(Count)
FOR Month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as P
)AS TempTable
ORDER BY Year