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

Performing UNPIVOT Operations
For obvious reasons, you're likely to use the UNPIVOT function much less often than the PIVOT function. However, in cases where you're given a set of source data in the form of a crosstab, the UNPIVOT function can be a valuable tool. The UNPIVOT function is predictably similar to the PIVOT function. First, identify the value_column and pivot_column, as well as which columns should be unpivoted. Using the data in Figure 2 as an example, the new value_column name for the unpivoted table would be OrderTotal and the pivot_column name would be OrderYear. If you think of the PIVOT and UNPIVOT expressions as "forward" and "reverse" processes, respectively, it's easy to see how the two work. The following code shows the resulting UNPIVOT SQL expression.

   SELECT
      ProductID, 
      OrderYear, 
      OrderTotal 
   FROM 
      (SELECT ProductID, 
         [2001] = Total_Sales_2001, 
         [2002] = Total_Sales_2002, 
         [2003] = Total_Sales_2003, 
         [2004] = Total_Sales_2004 
      FROM Pivoted_Order_Total) OrderTotal
   UNPIVOT (
      OrderTotal 
      FOR OrderYear 
         IN ([2001], [2002], [2003], [2004])
   ) AS unpvt

Author's Note. The preceding UNPIVOT expression would be simpler to read if you abstracted the table_source from the expression using a CTE.

While they are very small pieces in the overall SQL Server 2005 feature list, the PIVOT and UNPIVOT commands should greatly simplify many of the reporting processes that developers often work with. Among the many developer-focused enhancements added to SQL Server 2005 that get much of the new product's press, be careful not to overlook the many smaller-impact enhancements that can make your life just a little simpler.

Previous Page

---

• Download The Code for this Article

---