Pivoting can be useful when the data needs to be modelled/flipped before it gets to the target.
SQL Server offers the PIVOT function to enable a relational set of records to be summarised and presented in the style of a Pivot Table. this operator was introduced in SQL Server 2005.
Example of the data in a table :
OrderId OrderDate Amount ----------- ---------- ------ 1 2007-01-01 10.50 2 2007-01-26 12.50 3 2007-01-30 12.00 4 2007-02-14 13.75 5 2007-02-20 10.00 6 2007-03-06 15.00 7 2007-03-10 17.50 8 2007-03-29 20.00
After pivotting the data, it would look like this:
OrderYear Jan Feb Mar ----------- ----- ----- ----- 2007 35.00 23.75 52.50
Pivot operator for SQL Server 2005 and over:
SELECT OrderYear,
[1] AS 'Jan',
[2] AS 'Feb',
[3] AS 'Mar'
FROM (SELECT DATEPART(yyyy, order_date),
DATEPART(m, order_date),
amount
FROM Orders) AS O (OrderYear, month_nbr, amount)
PIVOT
(SUM(amount) FOR month_nbr IN ([1], [2], [3])) AS P;
Pivoting is still possible without using the PIVOT operator which was introduced in SQL Server 2005.
Using CASE
SELECT DATEPART(yyyy, order_date) AS OrderYear,
SUM(CASE WHEN DATEPART(m, order_date) = 1
THEN amount ELSE 0 END) AS 'Jan',
SUM(CASE WHEN DATEPART(m, order_date) = 2
THEN amount ELSE 0 END) AS 'Feb',
SUM(CASE WHEN DATEPART(m, order_date) = 3
THEN amount ELSE 0 END) AS 'Mar'
FROM Orders
GROUP BY DATEPART(yyyy, order_date)
