Shawn Weisfeld

I find when I talk to myself nobody listens. - Shawn Weisfeld
posts - 365, comments - 174, trackbacks - 34

My Links

News


Shawn Weisfeld's Facebook profile

The views expressed in this blog are mine and mine alone, not that of my employer, Microsoft, or anyone else’s. No warrantee is given for the quality of any material on this site.

Archives

Post Categories

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.

Print | posted on Monday, January 21, 2008 1:55 PM | Filed Under [ SQL ]

Feedback

Gravatar

# Pivot/Cross Tab Queries in SQL Server V2

3/10/2009 1:00 AM | drowningintechnicaldebt.com

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 1 and 1 and type the answer here:

Powered by: