PIVOT Columns Must Be Base Columns
None of PIVOT's columns (grouping, rotation, aggregation) can be directly an
expression; rather, they must all be base columns in the table provided to PIVOT
as input. It's obvious that the grouping columns are base columns since the
grouping columns are derived implicitly. The aggregation column can't be an
expression—for example, SUM (Quantity * UnitPrice). You can't even specify
COUNT ( *); instead, if you need to use the COUNT aggregate, you must specify
a base column name—for example, COUNT (Order-ID). Similarly, you can't
specify an expression as the rotation column—for example, YEAR (OrderDate)
IN ([1996], [1997], [1998]).
The workaround for all these limitations is to use a table expression similar
to using one to circumvent the implicit-grouping problem I described earlier.
To demonstrate the technique, suppose that you need to pivot data from the result
of a join between the Orders and Order Details tables. You want months on rows,
years on columns, and the sum of Quantity * Unit-Price in the intersections
of months and years, as Table 2 shows.
The code in Listing 5 uses a derived
table to provide a solution.
Another simple workaround is to use COUNT(some_column). The column can be either
a real column from the input table or a pseudo column in the table expression
derived from a constant. For example, the query in Listing
6 returns the output in Table 3
with the count of orders for each month and year.
Can't Rotate More than One Column
The PIVOT operator doesn't let you specify multiple IN clauses to rotate more
than one column. For example, you can't request to rotate both shippers and
years: FOR ShipVia IN([1], [2], [3]) AND OrderYear IN ([1996],[1997], [1998]).
As a workaround, in the table expression that you're preparing as input to
the PIVOT operator, you can concatenate the elements that you want to rotate.
For example, to rotate both shippers and years, you can use the expression at
callout A in Listing 7 to concatenate
the two. In the parentheses following the PIVOT operator (callout B), specify
FOR RotationCol IN([1_1996], [1_1997], [1_1998], [2_1996], [2_1997], [2_1998],[3_1996],[3_1997],[3_1998]).The
query in Listing 7 calculates the
sum of freight for each employee and shipper/year combination, with employees
on rows and shipper/year combinations on columns, and generates the output that
Table 4 shows.
Can't Specify Multiple Aggregations
PIVOT lets you specify only one aggregation. Suppose you need to get both the
sum of freight and count of orders for each employee and shipper, as Web
Table 1 (http://www.sqlmag.com,
InstantDoc ID 93907) shows. You can't do this directly by using a single PIVOT
operation, but as a workaround you can join the result sets of two PIVOT operations,
as Listing 8 shows.
The query in Listing 8 defines
two CTEs: The SumFreight CTE contains the sum of freight for each employee and
shipper, and the CountOrders CTE contains the count of orders for each employee
and shipper. The outer query joins SumFreight and Count-Orders based on a match
between the employee IDs and returns the columns with the sum of freight and
count of orders of each shipper.
Next Time: A More Complete Solution
I've described various limitations of the PIVOT operator and provided workarounds
for each limitation. Next time, I'll show you how to write a stored procedure
that encapsulates the logic and circumvents most limitations, thereby providing
a more complete and flexible solution to dynamic-pivoting needs.
End of Article
Prev. page
1
[2]
next page -->
