T-SQL Tuesday #16: PIVOT

First of all a big thanks to Jes Borland (Twitter | Blog) for hosting this month’s T-SQL Tuesday (#16) topic about aggregate functions.

Ok. So it’s not *really* an aggregate function, but it uses them and it does aggregate.

Yes, I am talking about the PIVOT function in SQL Server 2005+ that’s near and dear to all our hearts.

The syntax is a bit wonky and takes some getting used to:

[sql]FROM <yourtable>
PIVOT(<aggregate function such as SUM>(<aggregated column>)
FOR <distributed element>
IN (<specific distributed values>))
AS alias
[/sql]

So what does all of this mean?

The easiest way I found to approach the PIVOT feature is use the following steps:

1. Identify table (obviously)
2. Construct a Common Table expression to provide cleaner results (as we don’t want it to aggregate everything by default, only by the ones we want).
3. Identify the aggregated column and the function you want to perform on the aggregated column (such as SUM, AVG, MAX, etc)
4. Identify the filtered column(s) within the IN clause.

So let’s use AdventureWorks to break this down. I want to query the Sales.SalesPerson table for total SalesYTD for each territory.

First let’s query our data set to see what we need to PIVOT by:
[sql]
SELECT TerritoryID, SalesYTD
FROM Sales.SalesPerson
ORDER BY TerritoryID
[/sql]

I get this:

So using this we want to aggregate this data by territoryID and SUM the total SalesYTD.

So first let’s construct our CTE to provide aggregation only on the columns we want. This is pretty simple as it’s almost exactly what we just queried.
[sql]
WITH myCTE AS
(
SELECT TerritoryID, SalesYTD
FROM Sales.SalesPerson
)
[/sql]

Easy so far?

Now to identify our function we’ll want to use the SUM function on the SalesYTD column.
Then for our filter we will need all the territory IDs. In this case 1,2,3,4,5,6,7,8,9,10. Be aware this method will not cover our NULLs as this demo is just to showcase our known territories/values.

Let’s construct:
[sql]
SELECT * FROM myCTE
PIVOT
(
SUM(SalesYTD)
FOR TerritoryID
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
)
AS pivotout
[/sql]
Here’s the result:

Obviously this can be cleaned up but I’m sure you get the gist.

The PIVOT function is a nice feature as it pares down the T-SQL into a nicer less verbose format.

Hope this helps you understand the PIVOT function more.