New to SQL Server 2005 is the PIVOT operator, which gives you the ability to rotate row level data into tabular data without the use of the CASE statement, as was necessary in previous versions of SQL Server.
CASE statement queries
Transforming data from row-level data to columnar data is nothing new to database developers. In previous versions of SQL Server, a series of CASE statements were used along with a series of aggregation queries to arrive at the required cross-tab data set. While this approach gives the developer a high degree of control over the data returned, the resulting queries are often cumbersome to write.
In order to take a look at how these CASE statements are used, I'll need some test data. Run the scripts in Listing A to create the SalesHistory table and load data into the table.
Now I can construct our CASE statement-based PIVOT query. The goal of the query is to aggregate the sales by year for each product available: the BigScreen, PoolTable, and Computer. Each product will have its sales data available in a dedicated column. Listing B contains the script for the CASE statement query.
This query, while straightfoward and relatively easy to write, is cumbersome. You must explicitly type a CASE statement for each row you want to transform to tabular data, which is not much fun. It would be great if a construct would do this for you automatically—this is where the PIVOT operator comes into play.
PIVOT
With the new PIVOT operator in SQL Server 2005, the CASE statement and GROUP BY statements are no longer necessary. (Every PIVOT query involves an aggregation of some type, so you can omit the GROUP BY statement.) The PIVOT operator provides the same functionality that we tried to achieve with the CASE statement query, but you can achieve it through less code, and it is a bit more pleasing on your eyes. Listing C is a sample PIVOT query to mimic our CASE statement query.
The meat of this query is contained in the parentheses after the PIVOT operator. Inside the parentheses, we are using a SUM aggregate function to sum up the SalePrice for each Product listed in the FOR Product IN() statement. This statement may seem somewhat odd because the values in the IN() statement are not enclosed in tick (') marks to signify that they are literal values. These values are actually treated as ColumnNames, and they are transformed into tabular fields in the final result set.
If you run this query, you will get a result similar to the one from our CASE statement query. The big difference is that this query still returns one row for each row listed in our table, which is not desirable; we want a distinct list of rows for each year of sales listed in our table. The problem lies in the way we first constructed our PIVOT query.
In the SELECT statement in the query, we simply listed the year and a field for each product listed in our SalesHistory table. The problem is that the SELECT statement that precedes the PIVOT clauses cannot specify which columns to use in the PIVOT clause. The PIVOT clause applies the grouping to all columns that are not the pivoted or aggregated fields. We can use a subquery to achieve the desired results. Listing D features the resulting query. This small change allows us to mimic our CASE statement query with less code.
Limitations
The PIVOT operator is useful in many situations, yet there are drawbacks. Much of the functionality provided by the PIVOT operator requires you to "hard code" the fields that you are pivoting into columnar data. You can overcome this by building the PIVOT query through dynamic TSQL statements, but this is not the most desirable solution.
Another potential drawback of using the PIVOT clause is that you can only aggregrate one field in the result set; in the CASE statement queries, you can aggregate as many fields as needed.
While these potential setbacks may seem like a big deterrent from using the new PIVOT operator, it still has its place in the TSQL developers' toolbelt.
Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.
