A Very Simple PIVOT Example

When I first started working with the new PIVOT command in SQL Server 2005, it didn’t make any sense to me.  So I created some very simple examples on my own to help with my understanding.  Here is one.  First, I create the CTE (as I explained in my last post):

WITH Sales as (
SELECT  SalesOrderID, Name, OrderDate
FROM Sales.SalesTerritory st
INNER JOIN Sales.SalesOrderHeader soh
ON st.TerritoryID = soh.TerritoryID
WHERE OrderDate >= ’7/1/2001′
AND OrderDate < ’8/1/2001′
)

The CTE contains the sales orders and the territory name for that sale during the month of July 2001.  Next we will create the PIVOT.  The goal of the PIVOT is to count the number of sales by territory.  Not very exciting, but a good simple example:

SELECT Convert(varchar(20), OrderDate, 101) AS ‘Sales Date’,
Northwest,
Central,
Southwest,
Canada,
France,
Germany,
Australia
FROM sales  — the CTE above
PIVOT
(
COUNT (SalesOrderID)
FOR Name in ([Northwest], [Central], [Southwest], [Canada], [France], [Germany], [Australia])) as a
ORDER BY OrderDate

One downside to the PIVOT command is you need to know the column names at design time.  They must be hard coded.  The only way you can get around this is to use dynamic SQL.