SQL Server Magazine

Server's pivoting capabilities (also known as crosstab queries) have many practical applications. Given an input table expression, pivoting—which is essentially rotation of data—turns one column's values from multiple rows into multiple columns in a single result row. You can use pivoting in different ways, such as in reporting, custom aggregates, and relational division. Although SQL Server 2005's pivoting feature is useful, the PIVOT operator has some limitations. Here I'll show you some workarounds for these limitations, and next month I'll provide a flexible and more complete solution to dynamic-pivoting needs in SQL Server 2005. Before continuing, though, if you aren't familiar with the new PIVOT operator in SQL Server 2005, I suggest you read the Web-exclusive articles "Dynamic Pivoting," July 2004, InstantDoc ID 43140 and "Pivot (or Unpivot) Your Data," June 2004, InstantDoc ID 42901, which explain PIVOT; and "Dynamic Crosstab Queries," November 2000, InstantDoc ID 15608, which discusses dynamic pivoting in SQL Server 2000.

Implicit Grouping
Pivoting involves three phases: grouping, rotation, and aggregation.To understand these phases, let's consider an example. Suppose you're asked to pivot data from the Orders table in the Northwind database. You need to return the sum of freight for each employee and shipper, such that employee IDs (grouping column) would appear on rows, shipper IDs (rotation column) on columns, and the sum of freight (aggregation column) in the intersection of each employee and shipper, as Table 1 shows.

The grouping phase involves grouping the data by the grouping column EmployeeID. A row will be generated in the result for each employee. The rotation phase involves generating a result column for each element of the rotation column ShipVia. The aggregation phase involves aggregating the Freight column for each combination of grouping element (employee) and rotation element (shipper).

The PIVOT operator has a confusing aspect. Although you specify the rotation and aggregation columns explicitly in the PIVOT clause, SQL Server determines the grouping columns implicitly. The grouping columns are all columns from the table provided as input to the PIVOT operator, which aren't mentioned in the PIVOT clause as either aggregation or rotation columns. Thus, the following PIVOT query doesn't return the result you'd expect:

SELECT EmployeeID, [1], [2], [3] FROM dbo.Orders
  PIVOT(SUM(Freight)
  FOR ShipVia IN([1], [2],
  [3])) AS P; SQL 

(Some code in this article wraps to multiple lines because of page constraints.) Instead of getting nine rows in the result (one row for each employee) as Table 1 shows,you get 830 rows in the result: a row for each order instead of a row for each employee. Since the columns Freight and ShipVia were specified in the PIVOT clause as aggregation and rotation columns, respectively, SQL Server implicitly used all other columns in the table for grouping.

The workaround for the implicit-grouping limitation is simple. Instead of querying the base table (e.g., Orders) directly, provide the PIVOT operator a table expression (derived table or common table expression—CTE) as the input table, as Listing 1 shows. The table expression contains only the columns required for pivoting: grouping, rotation, and aggregation.

PIVOT Isn't Dynamic
You must specify a static list of rotation elements in the parentheses following the rotation column and the IN keyword. For example, the previous PIVOT query specifies the static list of shipper IDs [1],[2],[3]. You can't specify a subquery or ask SQL Server to determine the distinct rotation elements. This means that with a static query, you must know in advance which elements you want to rotate.

The only workaround for this limitation is to use dynamic SQL.You query the distinct elements that you need to rotate, construct the PIVOT query string, and execute the query string by using sp_executesql or EXEC.The code in Listing 2 demonstrates how to construct the list of shipper IDs with a FOR XML PATH query. This code returns the string [1],[2],[3].

There are other techniques you can use to concatenate strings (e.g., by using a cursor), but the technique that uses the FOR XML PATH option is by far simpler and faster than the alternatives. (I describe this technique in "Custom Aggregations: Specialized Solutions," July 2006, InstantDoc ID 50269.)

If you don't have a table like Shippers with a row for each distinct rotation element, you can query the distinct rotation elements from the table used as the input to the PIVOT operator (Orders, in our case), as the code in Listing 3 shows. You can then construct the full query string and execute it as Listing 4 shows.

   Prev. page   [1] 2     next page