Executive Summary:
Many people find the PIVOT operator syntax in SQL Server Books Online (BOL) hard to understand. The syntax in SQL Server Books Online uses a derived table as the basis for the PIVOT query. What SQL Server Books Online doesn't point out is that you can use a common table expression (CTE) instead. Kathi Kellenberger walks you through creating a PIVOT query that uses a common table expression. |
The PIVOT operator, which was introduced in SQL Server 2005, lets you create results that are pivoted, essentially using the data from one of the columns as column headers. For example, suppose you want to create a report that breaks down sales by year and month so that you can compare sales months for different years. Using the 2005 or 2008 version of the AdventureWorks database, you can create a query summarizing the data with the code in Listing 1.
Listing 1: Query that Summarizes Sales by Year and Month |
 |
Table 1 shows an excerpt from the results. As you can see, looking for trends by month isn't easy.
Table 1: Partial Results from the Query in Listing 1
| TotalDue |
YearOrdered |
MonthOrdered |
| 1172359.4289 |
2001 |
7 |
| 2605514.9809 |
2001 |
8 |
| 2073058.5385 |
2001 |
9 |
| ... |
|
|
| 3781879.0708 |
2002 |
7 |
| ... |
|
|
Table 2 shows the results as you would like to see them.
Table 2: The Pivoted Results
| Year |
January |
February |
March |
... |
November |
December |
| 2001 |
NULL |
NULL |
NULL |
... |
3690018.6652 |
3097637.3384 |
| 2002 |
1605782.1915 |
3130823.0378 |
2643081.0798 |
... |
4427598.0006 |
3545522.7380 |
| 2003 |
2233575.1127 |
3705635.4979 |
2611621.2596 |
... |
5961182.6761 |
6582833.0438 |
| 2004 |
3691013.2227 |
5207182.5122 |
5272786.8106 |
... |
NULL |
NULL |
In Table 2, the data is summarized and displayed so that the months can be easily compared from year to year. I'll explain how to write queries that use the PIVOT operator to produce the results shown in Table 2.
Note that I won't be using the PIVOT syntax shown in SQL Server Books Online (BOL) because that syntax can be difficult to understand at first glance. The syntax in BOL uses a derived table as the basis for the PIVOT query. What BOL doesn't point out is that you can use a common table expression (CTE) instead. Listing 2 shows the syntax for a PIVOT query that uses a CTE.
Listing 2: Syntax for a PIVOT Query that Uses a CTE |
 |
As you can see, there are two main parts: a base query (callout A) and a PIVOT expression (callout B). This syntax might look intimidating, but I'll guide you through it step by step.