Pivot/Cross Tab Queries in SQL Server
There are many ways to turn rows of data into columns in SQL Server, I figured I would outline some of them and talk about some of the pro/cons of each.
Sample 1: Using the SQL Server 2005 PIVOT operator. This is a great feature of SQL Server 2005 but has 2 obvious limitations. First it requires you to know the items you are going to pivot over (you can get around this limitation by using dynamic SQL). Second it requires you to have SQL Server 2005.
--Sample 1
SELECT CustomerID, [1996] AS year1996, [1997] AS year1997, [1998] AS year1998
FROM
(
SELECT CustomerID, OrderID, DATEPART(year, OrderDate) AS years
FROM Orders) N
PIVOT
(
COUNT (OrderID)
FOR years IN
(
[1996], [1997], [1998] )
) AS pvt
ORDER BY CustomerID
Sample 2: We can work around both of the limitations of the first sample by using our old friend the CASE statement. But since we have declared a variable we are limited to stored procedures. This is due to the fact that you cannot embed a sub-query in an aggregate function.
--Sample 2
DECLARE @year0 int
SET @year0 = (SELECT MIN(DATEPART(year, OrderDate)) FROM [Northwind].[dbo].[Orders])
SELECT
CustomerID,
SUM(CASE WHEN DATEPART(year, OrderDate) = (@year0) THEN 1 ELSE 0 END) AS year_0,
SUM(CASE WHEN DATEPART(year, OrderDate) = (@year0 + 1) THEN 1 ELSE 0 END) AS year_1,
SUM(CASE WHEN DATEPART(year, OrderDate) = (@year0 + 2) THEN 1 ELSE 0 END) AS year_2
FROM Orders
GROUP BY CustomerID
Sample 3: However we can trick SQL Server into allowing us to embed the sub query if we hide it in a view.
--Sample 3
CREATE VIEW Step1
AS
SELECT
CustomerID,
(CASE WHEN DATEPART(year, OrderDate) = (SELECT MIN(DATEPART(year, OrderDate)) FROM Orders) THEN 1 ELSE 0 END) AS year_0,
(CASE WHEN DATEPART(year, OrderDate) = (SELECT MIN(DATEPART(year, OrderDate)) + 1 FROM Orders) THEN 1 ELSE 0 END) AS year_1,
(CASE WHEN DATEPART(year, OrderDate) = (SELECT MIN(DATEPART(year, OrderDate)) + 2 FROM Orders) THEN 1 ELSE 0 END) AS year_2
FROM Orders
CREATE VIEW Step2
AS
SELECT
CustomerID,
SUM(year_0) AS year_0,
SUM(year_1) AS year_1,
SUM(year_2) AS year_2
FROM Step1
GROUP BY CustomerID
SELECT *
FROM Step2
So with these variations you have a few extra tools in your tool box for converting rows into columns.