PIVOT in SELECT

By Kalman Toth, Business Intelligence Architect

September 8, 2007

Dream of generations of SQL developers will come true with the PIVOT feature of SQL Server 2005: the ability to produce crosstab reports. While in an Excel spreadsheet you can pivot the rows and columns, SQL prefers to go down vertically with tables and results, not across like the columns. Business Intelligence tools, such as Cognos or Business Objects, have crosstab reporting as a major feature.

By pivoting the data set on the selected sales staff (EmployeeID) column, the Pivot transformation can output a data set with a single row per dealer. Since there are 5 selected salesperson, the relational representation of the data would be 5 times as many rows.  Certainly harder to read and comprehend than the pivoted data which is cross tabulated. As a direct benefit of the PIVOT operation it is very easy to compare the performance of sales staff in general and for a particular dealer.

Any well organized, senior SQL developer may protest: but the code is hardwired! Yes it is! The query is full with data which should reside in tables not in the query. One way to eliminate the code hardwiring is with dynamic SQL.

A second concern about PIVOT is that not easy to remember the syntax. A simple solution for that starting with an example such as the following and modifying it to reach the design goal.

This is the code sample for sales counts for dealers by staff:

USE AdventureWorks
GO
SELECT DealerName,
[164] AS Susan,
[198] AS Harry,
[223] AS Jake,
[231] AS Simone,
[233] AS Kevin
FROM
(SELECT PurchaseOrderID, EmployeeID, DealerName=v.Name
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.Vendor v
ON poh.VendorID=v.VendorID) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY DealerName;
GO

This is a subset of the result:

DealerName	Susan	Harry	Jake	Simone	Kevin
Advanced Bicycles	4	3	5	5	4
Allenson Cycles	5	3	5	5	4
American Bicycles	5	1	5	5	5
American Bikes	6	2	7	9	6