SQL: The One And Only Amazing PIVOT!
Author: jason
Date: 2014-02-07 16:58:01
Category: Musing

Why did I not know about the PIVOT operator sooner? Is sooner a word? Merriam-Webster says Yes, a native of Oklahoma. That's a relief.

The PIVOT operator (since SQL 2005!) can turn Rows into Columns. Huzzah!




Code

DECLARE @Sales AS TABLE (Sale_Date DATE, Sale_Price MONEY)
INSERT INTO @Sales VALUES ('2014-01-25', '1000.00'), ('2014-02-14', '800.00'), ('2014-03-01', '4000.00'),
('2014-01-15', '500.00'), ('2014-02-10', '80.00'), ('2013-12-01', '4500.00')
SELECT * FROM @Sales;

WITH C AS (
SELECT YEAR(Sale_Date) AS Sale_Year, MONTH(Sale_Date) AS Sale_Month, Sale_Price
FROM @Sales
) SELECT * FROM C
PIVOT (SUM(Sale_Price)
FOR Sale_Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS P
ORDER BY Sale_Year DESC;


Pivot: http://technet.microsoft.com/en-us/library/ms177410(v=sql.90).aspx




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009