Create Pivoted Tables in 3 Steps

At this point, you need to add the column names to the SELECT list in the main query. In this case, the columns are OrderYear and the pivoted columns. You should not list the aggregated column, TotalDue, so the SELECT list looks like

SELECT OrderYear,January,February,
  March,April,May,June,July,August,
  September,October,November,December

The pivoted columns will display in the order that they're listed in the SELECT clause. Listing 4 shows the completed PIVOT query, which created the pivoted results in Table 2.

Listing 4: The PIVOT Expression

There are many ways the PIVOT query can vary. Take, for example, the PIVOT query in Listing 5.

Listing 5: Another Example of a PIVOT Query

This query varies from the one in Listing 4 two ways:

• It uses aliased column names. In this query, the numeric month of the order date is used in the base query. The pivoted column names are aliased in the SELECT list so that the column headers are month names rather than month numbers. To save typing, it's advantageous to produce the desired column headings in the base query, which in this case is the CTE.
• It uses an ORDER BY clause to order the returned data. The ORDER BY clause needs to go after the PIVOT expression. The only columns allowed in the ORDER BY clause are those that actually show up as columns in the results. You can include the columns used for grouping and those specified in the IN list. For example, this query uses ORDER BY January DESC to display the results in order of highest to lowest sales in January. If you want to display the year in descending order, you would put ORDER BY OrderYear DESC after the PIVOT expression.

Although the PIVOT operator might look intimidating, writing a PIVOT query isn't that difficult if you take it step by step. The PIVOT operator is perfect for pivoting results when the pivoted columns aren't likely to change. Although it's disappointing that the PIVOT operator isn't dynamic, the PIVOT operator is still a very useful tool. (A dynamic PIVOT operator is on my wish list for the next release of SQL Server since the feature didn’t make it into SQL Server 2008.)