Swap row/column of pivot query

Question

I want to know how I can flip the rows/columns of this query:

SELECT *
FROM (SELECT YEAR(DATA_DOC) AS YEAR, DATENAME(MONTH, DATA_DOC) AS MONTH, SUM(IMP) AS TOTAL
        FROM MyTable
        WHERE YEAR(DATA_DOC) BETWEEN 2011 AND 2014
        GROUP BY YEAR(DATA_DOC), DATENAME(MONTH, DATA_DOC)
    ) AS REPORT
PIVOT
(
    SUM(TOTAL) FOR MONTH IN (January,....ecc...)
) AS REPORT_T

This query returns the results in this way:

Year       |    January    |     February      |      ecc.....
2011       |     1000      |        500        |      ....
2012       |      250      |        1110       |       ....
2013       |      0        |        150        |       .... 
2014       |     880       |        450        |       ....

I can reverse the columns and rows so that I get:

Month      |    2011       |        2012       |       2013
January    |     1000      |        500        |      ....
February   |      250      |        1110       |       ....
...        |      0        |        150        |       .... 
...        |     880       |        450        |       ....

I should probably use the UNPIVOT but I can not apply it.

The problem here is that year, unlike month, is not a defined set. Because you have n possibilities for year, the piviot must use dynamic SQL. @BlueFeet is usually all over these questions so I'll just point you back to one of his/her excellent answers: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — xQbert, May 07 '15 at 12:36

Giorgos Betsos · Accepted Answer · 2015-05-07T18:45:17.287

1

Simply put year values in place of months:

SELECT *
FROM (SELECT YEAR(DATA_DOC) AS YEAR, DATENAME(MONTH, DATA_DOC) AS MONTH, SUM(IMP) AS TOTAL
      FROM MyTable
      WHERE YEAR(DATA_DOC) BETWEEN 2011 AND 2014
      GROUP BY YEAR(DATA_DOC), DATENAME(MONTH, DATA_DOC)
    ) AS REPORT
PIVOT
(
    SUM(TOTAL) FOR YEAR IN ([2011],[2012],[2013],[2014])
) AS REPORT_T

SQL Fiddle Demo

If you want to produce exactly 12 rows, one for each month, even if there is no data available for some of the months, then you can modify the above query to:

 SELECT x.m, t.[2011], t.[2012], t.[2013], t.[2014]
 FROM (VALUES ('January'), ('February'), ('March'), ('April'), ...etc) x(m)
 LEFT JOIN (
   SELECT *
   FROM (SELECT YEAR(DATA_DOC) AS YEAR, 
                DATENAME(MONTH, DATA_DOC) AS MONTH, 
                SUM(IMP) AS TOTAL
         FROM MyTable
         WHERE YEAR(DATA_DOC) BETWEEN 2011 AND 2014
         GROUP BY YEAR(DATA_DOC), DATENAME(MONTH, DATA_DOC)
        ) AS REPORT
   PIVOT
   (
       SUM(TOTAL) FOR YEAR IN ([2011],[2012],[2013],[2014])
   ) AS REPORT_T ) AS t ON x.m = t.MONTH

SQL Fiddle Demo

edited May 07 '15 at 18:45

answered May 07 '15 at 12:36

Giorgos Betsos

71,379
9
63
98

@user2263764 So you want missing months to appear in the result set as well? – Giorgos Betsos May 07 '15 at 15:05
Yes, like the previous query ... In the previous one, if there wasn't correspondence with year / month, it was marked with NULL, but the months appeared all. Probably because the set was finished (SUM (TOTAL) FOR MONTH IN (January, .... etc ...)) – user2263764 May 07 '15 at 17:23
@user2263764 You can have a look at the edit I made. – Giorgos Betsos May 07 '15 at 18:48
Exactly what I asked! I a bit modified the query to have ordered months! ;) Thank you very much !! – user2263764 May 08 '15 at 11:43

Swap row/column of pivot query

1 Answers1