SQL Server Pivot Function

In previous post we discussed exporting data from SQL Server into an excel file and how you can pivot the data etc. Pivoting is basically a way for you to convert rows into columns. Starting with 2005, SQL Server introduced PIVOT function which allows you to retrieve columns in tabular format. PIVOT function doesn't work on certain data types such as XML.

In the past, suppose you wanted to group employees by the city they reside in. You can use Group By clause to get the count of the number of employees by city, but such a query will result in each city as one row and you will have to do something extra to convert them into columns. The query below will return the number of employees that reside in three cities with the count for each city appearing in one row.

USE AdventureWorks
GO

SELECT City,COUNT(*) AS [Count] FROM HumanResources.vEmployee 
WHERE City IN ('Monroe','Redmond','Seattle')
Group By City

The result will look like this

City	Count
Monroe	14
Redmond	21
Seattle	44

Suppose you want to pivot this data so that each city appears as a column. You can modify the above query and use PIVOT function.

Use AdventureWorks
GO
SELECT [Monroe],[Redmond],[Seattle]
FROM
(
    SELECT E.City FROM HumanResources.VEmployee E
) C
PIVOT
( 
 COUNT (City)
 FOR City
 IN ([Monroe],[Redmond],[Seattle])
 ) AS PivotTable;

Now the result will look like this

Monroe	Redmond	Seattle
14	21	44

You may be wondering about missing WHERE clause. Basically FOR City IN () under PIVOT function takes care of filtering your data based on your criteria. This query works but the nested query still returns all rows. You can add a WHERE clause in your nested query to only return rows that satisfy your criteria. Above query can be rewritten as follows...

Use AdventureWorks
GO
SELECT [Monroe],[Redmond],[Seattle]
FROM
(
    SELECT E.City FROM HumanResources.VEmployee E
    WHERE City IN ('Monroe','Redmond','Seattle')
) C
PIVOT
( 
 COUNT (City)
 FOR City
 IN ([Monroe],[Redmond],[Seattle])
 ) AS PivotTable;

PIVOT is a useful function but not very intuitive, as you can see above, it takes a minute to make sense of it. Question for you guys - have you used PIVOT function before and if so, how did you use it?

Thank you.