Using SQL Server PIVOT feature to transform rows into columns
PIVOT queries in SQL Server are useful when we need to transform data from row-level to column-level.
Example: Imagine having the table named Invoice (payments per month) – for simplicity let’s say that we have PaidDate and Amount.
Id PaidDate Amount 1 2014-01-05 12:34:22.000 318.22 2 2014-01-06 11:44:00.000 128.33 3 2014-04-07 16:44:00.000 55.50 4 2014-02-08 15:12:00.000 88.55 5 2014-02-16 18:39:00.000 115.45 6 2014-03-01 16:27:00.000 9.99 7 2014-03-08 13:13:00.000 16.99 8 2014-04-27 16:44:00.000 568.69 9 2014-05-10 19:40:00.000 43.65
What we wanted now is to get total money collected from the beginning of the year 2014, and in order to do this we can use this PIVOT query:
SELECT * FROM ( SELECT YEAR(PaidDate) AS [Year], LEFT(DATENAME(month, PaidDate), 3) AS [Month], Amount FROM Invoice ) as PaymentsByYear PIVOT ( SUM(Amount) FOR [Month] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC) ) AS PaymentsByYear ORDER BY [Year] DESC
And we finally get
Year JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 2014 446.55 204.00 26.98 624.19 43.65 NULL NULL NULL NULL NULL NULL NULL