Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl : Page 3

Using PIVOT Commands
As stated previously, PIVOT expressions in SQL Server 2005 provide developers with the ability to rotate row data into columns. Because the use of sales data is commonplace during historical analysis, I'll use this theme as the basis of my samples.

To use the PIVOT feature, first decide which column contains the important "values" for the query. In this example, the important piece of information is sales dollar amounts. Second, because the example analyzes sales over a period of time, it pivots the sales year field data into columns in the response. You also need to select which field values in the pivot column should be pivoted to form columns.

Figure 3. ProductSales CTE Results: The figure shows the results of running the ProductSales CTE.

This example uses the AdventureWorks sample database tables named Sales.SalesOrderDetail and Sales.SalesOrderHeader. Joining these two tables using a CTE gives the results displayed in Figure 3. Note that there is one row per Sales.SalesOrderDetail row. Here's the CTE:

   WITH ProductSales(ProductID, OrderYear, OrderTotal)
   AS (
      SELECT 
         det.productID, 
         YEAR(hdr.orderdate), 
         det.linetotal
      FROM 
         sales.salesorderdetail det
         JOIN sales.salesorderheader hdr 
   ON det.salesorderid = hdr.salesorderid 
   )

Using the ProductSales CTE, we can construct a PIVOT SQL expression. SQL Server 2005 Books Online defines the syntax of a PIVOT expression as:

   <pivoted_table> ::= 
      table_source PIVOT <pivot clause> table_alias
   <pivot_clause> ::= 
      ( aggregate_function ( vaule_column ) 
         FOR pivot_column 
         IN ( <column_list> )

Drawing from the ProductSales CTE, the value column will be the OrderTotal column. You'll be summing the OrderTotal values, so you need to use the SUM() aggregate function. The pivot column will be OrderYear, and you want to create a list of OrderYear columns for the years 2001, 2002, 2003, and 2004. In the select list for the SQL expression, rename the 2001, 2002, 2003, and 2004 values to Total_Sales_2001, Total_Sales_2002, and so on, making the column data more readable by consumers of the query. Here's the complete SQL expression using PIVOT. You can see the result of the query in Figure 2.

   SELECT 
      ProductSalesPivot.productID,
      Total_Sales_2001 = ISNULL([2001], 0), 
      Total_Sales_2002 = ISNULL([2002], 0), 
      Total_Sales_2003 = ISNULL([2003], 0), 
      Total_Sales_2004 = ISNULL([2004], 0)
   FROM 
      ProductSales
   PIVOT ( 
      SUM(OrderTotal)
      FOR OrderYear IN ([2001], [2002], [2003], [2004])
   ) AS ProductSalesPivot
   ORDER BY 
      ProductSalesPivot.ProductID

Notice in the preceding code that the SUM aggregate function is a required component of the PIVOT expression. In fact, the CTE could easily have been coded to SUM the linetotal values—when grouped by year, it would seem reasonable that the PIVOT expression could pivot using only the OrderTotal, without the need for the SUM function. However, the PIVOT expression requires an aggregate function.

Also note that the data specified in the IN column list must include all the values that will be pivoted into columns to be named explicitly. I would like to see a more elegant implementation where either all the values could be implicitly added by SQL Server, or where you could specify ranges or lists of values. However, at the moment, you must name each column value explicitly.

When building the PIVOT result set, SQL Server automatically adds a GROUP BY clause that groups by all the columns from the table_source that are not either the value_column or the pivot_column. Due to this grouping, the table_source should contain only columns that are of interest in the final query. You could achieve this using a View containing only the relevant columns, but using a CTE serves the same purpose.

Note that the complete query includes both the CTE and the PIVOT SQL query as shown below.

   WITH ProductSales(ProductID, OrderYear, OrderTotal)
   AS (
      SELECT 
         det.productID, 
         YEAR(hdr.orderdate), 
         det.linetotal
      FROM 
         sales.salesorderdetail det
         JOIN sales.salesorderheader hdr 
   ON det.salesorderid = hdr.salesorderid 
   )
   
   SELECT 
      ProductSalesPivot.productID, 
      Total_Sales_2001 = ISNULL([2001], 0), 
      Total_Sales_2002 = ISNULL([2002], 0), 
      Total_Sales_2003 = ISNULL([2003], 0), 
      Total_Sales_2004 = ISNULL([2004], 0)
   FROM 
      ProductSales
   PIVOT ( 
      SUM(OrderTotal)
      FOR OrderYear IN ([2001], [2002], [2003], [2004])
   ) AS ProductSalesPivot
   ORDER BY 
      ProductSalesPivot.ProductID

Figure 4. Adding a Product Name: When you run the query after modifying it to add a product name, you'll see these results.

It is possible to JOIN the PIVOT results to other tables. In the example above, many consumers of the query would find it useful to include a product name in the response to make the query response more readable. When joining to a pivoted table, you must ensure that you join to the pivoted table's table_alias, and not to the table_source for the PIVOT function.

The query below shows how you'd modify the preceding SQL expression to add a product name to the results. Figure 4 shows the results.

   SELECT 
      ProductID        = ProductSalesPivot.productID,
      Product_Name     = prd.Name, 
      Total_Sales_2001 = ISNULL([2001], 0), 
      Total_Sales_2002 = ISNULL([2002], 0), 
      Total_Sales_2003 = ISNULL([2003], 0), 
      Total_Sales_2004 = ISNULL([2004], 0)
   FROM 
      ProductSales
   PIVOT ( 
      SUM(OrderTotal)
      FOR OrderYear IN ([2001], [2002], [2003], [2004])
   ) AS ProductSalesPivot
      JOIN Production.Product prd ON ProductSalesPivot.productID = prd.productID
   ORDER BY 
      ProductSalesPivot.ProductID

Previous Page

Next Page

---

• Download The Code for this Article

---