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.
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
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