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
|
1: SELECT Year,
2: SUM(Jan) AS Jan,
3: SUM(Feb) AS Feb,
4: SUM(Mar) AS Mar,
5: SUM(Apr) AS Apr,
6: SUM(May) AS May,
7: SUM(Jun) AS Jun,
8: SUM(Jul) AS Jul,
9: SUM(Aug) AS Aug,
10: SUM(Sep) AS Sep,
11: SUM(Oct) AS Oct,
12: SUM(Nov) AS Nov,
13: SUM(Dec) AS Dec
14: FROM (
15: SELECT Year,
16: Jan = CASE MONTH WHEN 1 THEN Count end,
17: Feb = CASE MONTH WHEN 2 THEN Count end,
18: Mar = CASE MONTH WHEN 3 THEN Count end,
19: Apr = CASE MONTH WHEN 4 THEN Count end,
20: May = CASE MONTH WHEN 5 THEN Count end,
21: Jun = CASE MONTH WHEN 6 THEN Count end,
22: Jul = CASE MONTH WHEN 7 THEN Count end,
23: Aug = CASE MONTH WHEN 8 THEN Count end,
24: Sep = CASE MONTH WHEN 9 THEN Count end,
25: Oct = CASE MONTH WHEN 10 THEN Count end,
26: Nov = CASE MONTH WHEN 11 THEN Count end,
27: Dec = CASE MONTH WHEN 12 THEN Count end
28: FROM MyTable
29: ) AS TempTable
30: GROUP BY Year
31: ORDER BY Year
Using Pivot in TSQL:
1: SELECT Year,
2: [1] AS Jan,
3: [2] AS Feb,
4: [3] AS Mar,
5: [4] AS Apr,
6: [5] AS May,
7: [6] AS Jun,
8: [7] AS Jul,
9: [8] AS Aug,
10: [9] AS Sep,
11: [10] AS Oct,
12: [11] AS Nov,
13: [12] AS Dec
14: FROM
15: (
16: SELECT * FROM MyTable
17: PIVOT (SUM(Count)
18: FOR Month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as P
19: )AS TempTable
20: ORDER BY Year
Comments
Post a Comment