 |
|||||||||
|
|||||||||
|
|
I've been asked many times, by clients and friends alike, how to "pivot" a SQL resultset from a set of rows to a set of columns. This is something that many people do once a blue moon, and then forget how to do it. So, I decided to place an example on my blog for easy reference.
As I was doing this, I decided to include an example of how SQL Server 2005's new PIVOT operator would change the T-SQL code. I fully expected that the PIVOT syntax would be easier and cleaner. Judge the results for yourself. I will say that the PIVOT syntax wound up being more involved than I expected.
These two SQL statements are intended to list the number of orders placed in the year 2002, with the results broken down by month. Each month's data is returned in a separate column.
NOTE: These examples use the new AdventureWorks database in SQL Server 2005. Originally, I tried to do this with the Northwind database. The PIVOT operator fails with a syntax error when used against the Northwind database. This is because the Northwind database is set to SQL Server 2000 compatibility mode. (Yes, I wasted a good bit of time "remembering" this fact.)
SQL Server 2000
SELECT
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2002
SQL Server 2005
SELECT
[1] AS 'January'
,[2] AS 'February'
,[3] AS 'March'
,[4] AS 'April'
,[5] AS 'May'
,[6] AS 'June'
,[7] AS 'July'
,[8] AS 'August'
,[9] AS 'September'
,[10] AS 'October'
,[11] AS 'November'
,[12] AS 'December'
FROM (
SELECT
MONTH(OrderDate) AS 'MonthNumber'
,COUNT(*) AS 'OrderCount'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2002
GROUP BY MONTH(OrderDate)
) AS Data
PIVOT(
SUM(OrderCount)
FOR MonthNumber IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PVT
