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. Table 1 shows an excerpt from the results. As you can see, looking for trends by month isn't easy.

Listing 1: Query that Summarizes Sales by Year and Month
SELECT SUM(TotalDue) TotalDue, YEAR(OrderDate) AS YearOrdered,
  MONTH(OrderDate) AS MonthOrdered
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate),MONTH(OrderDate)
ORDER BY YEAR(OrderDate),MONTH(OrderDate)

 

TotalDue YearOrdered  MonthOrdered
Table 1: Partial Results from the Query in Listing 1

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. 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.

Year

January

February

March

...

November

December

Table 2: The Pivoted Results

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

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. 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.

Listing 2: Syntax for a PIVOT Query that Uses a CTE
BEGIN CALLOUT A
-- The base query
WITH AS (