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

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.

any major product releases have a dizzyingly long list of new features and enhancements—often introduced atop a product that is in many cases so complex and feature-ridden that it's already difficult to keep up with the breadth of the product. However, the wide feature sets also mean that most developers find small nuggets of functionality that often have a significant impact on their jobs, if only to make life a little simpler. It is in these nuggets that I think many developers first come to appreciate new product releases.

For me, two particularly interesting new features of Microsoft's upcoming release of SQL Server 2005 are its new PIVOT and UNPIVOT commands. From a manager's perspective, pivoting provides a considerably simpler view of data, truly transforming it into information. From a developer's perspective, the features let you rotate row data into columns in the query response to create pivoted or "crosstabbed" views of query results.

Before going on, it's worth taking a look at two options for viewing historical information; this quick overview will provide a good look at how useful pivoting can be. After that, you'll see the basics of pivoting data using SQL Server 2005, as well as how to unpivot data that's already crosstabbed.

Author's Note: The information provided in this article is based on the Beta 2 version of SQL Server 2005. I don't anticipate major changes to the syntax of the pivoting commands described in this article, but be aware that beta products often change before final release. Further, the sample data I chose to use for this article is taken from the AdventureWorks sample database included with SQL Server 2005. To follow along with the article, you should install the AdventureWorks sample database during the SQL Server 2005 installation process.
Figure 1: Sample Response: This response from a typical historical sales total query displays the sales total for an individual year on each row.

Traditional "Flat" Query
The first, and simplest, option for returning historical data is to query the data, grouping the fact information in a way that answers the questions being asked. The query shown below retrieves data from sales tables, and provides total sales figures by product by year. Figure 1 shows a portion of the response from this type of query. Note that it's difficult to see trends of sales between years. Using a traditional flat query, it would be even more difficult to answer questions about how sales of one product compared to sales of other products.

      ProductID   = det.productID, 
      SALES_YEAR  = YEAR(hdr.orderdate), 
      TOTAL_SALES = SUM(det.linetotal)
      sales.salesorderdetail det
      JOIN sales.salesorderheader hdr 
      ON det.salesorderid = hdr.salesorderid 
Figure 2: Historical Sales Data: The figure shows how the response to a query using a pivoted view of the data can be easier to read.
ORDER BY det.productID, YEAR(hdr.orderdate)

Note that you can visualize the same data far more easily if you pivot the SALES_YEAR column to create columns of TOTAL_SALES for each year. The resulting table is generally referred to as a crosstab. Figure 2 shows the same sales information pivoted to display the data in a crosstab format.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date