RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

Sometimes it's the little things that can simplify your life the most. Learn to combine SQL Server's PIVOT and UNPIVOT commands with Common Table Expressions to make your T-SQL code easier to read and produce more useful crosstabbed results.

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, 
         [2001] = Total_Sales_2001, 
         [2002] = Total_Sales_2002, 
         [2003] = Total_Sales_2003, 
         [2004] = Total_Sales_2004 
      FROM Pivoted_Order_Total) 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.

Michael S. Jones is the Director of Systems Architecture for Passport Health Communications, Inc., a national healthcare technology provider connecting hospitals, physician clinics and outpatient centers with payer and patient information to facilitate and improve their revenue cycle process. Michael, his wife, and three children live in Franklin, Tennessee where he spends his spare time reading and enjoying time with his family outdoors. .
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date