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.
SELECT ProductID = det.productID, SALES_YEAR = YEAR(hdr.orderdate), TOTAL_SALES = SUM(det.linetotal) FROM sales.salesorderdetail det JOIN sales.salesorderheader hdr ON det.salesorderid = hdr.salesorderid GROUP BY det.productID, YEAR(hdr.orderdate)
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)共2页 第1页 第2页
