CODEWITHSUNDEEP
sql
stighy
stighy

Reputation: 7170

Sql Server: how to transpose row to column?

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

Upvotes: 0

Views: 3606

Answers (1)

Yahia
Yahia

Reputation: 70369

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

Upvotes: 1

Related Questions