any major product releases have a dizzyingly long list of new features and enhancementsoften 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
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.
Traditional "Flat" Query
|Figure 1: Sample Response: This response from a typical historical sales total query displays the sales total for an individual year on each row.|
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)
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.|
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.