Sql Server: how to transpose row to column?

Question

this is (maybe) an already asked question... but i've some difficul to solve my problem. I need to transpose to column a row-based query. I explain: This is my actual query result

Year     Month     Value
2010      Jan         19
2010      Feb         10
...

I need

Year      Jan        Feb         ...         Dec
2010      19         20          ...         ...
2011      11         ..          ...

Actually, my main query is very simple..

SELECT     SUM(QTYCALC) AS TOT, YEAR(SCHEDSTART) AS MyYear, MONTH(SCHEDSTART) AS MyMonth
FROM         PRODTABLE
GROUP BY YEAR(SCHEDSTART), MONTH(SCHEDSTART)

Thanks in advance

Each RDBMS has a different solution. Please, post what database brand are you using. — dani herrera, Nov 09 '11 at 08:50

Yahia · Accepted Answer · 2011-11-09T09:40:44.257

You need to use PIVOT to achieve that - for explanation and some examples see http://msdn.microsoft.com/en-us/library/ms177410.aspx and http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Another option would be:

SELECT
YEAR(SCHEDSTART) AS MyYear, 
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 1 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Jan,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 2 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Feb,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 3 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Mar,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 4 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Apr,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 5 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS May,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 6 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Jun,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 7 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Jul,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 8 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Aug,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 9 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Sep,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 10 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Oct,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 11 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Nov,
(SELECT SUM(X.QTYCALC) FROM PRODTABLE X WHERE MONTH(X.SCHEDSTART) = 12 AND YEAR(X.SCHEDSTART) = YEAR(P.SCHEDSTART)) AS Dec
FROM       PRODTABLE P
GROUP BY YEAR(P.SCHEDSTART)

You should check both (PIVOT and this) regarding performance/execution plan...

Sql Server: how to transpose row to column?

1 Answers1