About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Saturday, September 15, 2007

PIVOT Operator Example

    Starting with SQL Server 2005, Microsoft introduced the PIVOT operator, which tries to implement the pivoting feature available in Excel, however it does only static pivoting, the values on which the pivoting is done must be mentioned explicitely.
    In List Based Aggregations I gave an example of a query in which the On Hand was cumulated per Location, the same output can be obtained with the below query:
SELECT Name
, ProductNumber
, MakeFlag
, FinishedGoodsFlag
, IsNull([Paint Storage], 0) OnHandPaintStorage
, IsNull([Frame Forming], 0) OnHandFrameForming
, IsNull([Frame Welding], 0) OnHandFrameWelding
, IsNull([Debur and Polish], 0) OnHandDeburPolish
, IsNull([Specialized Paint], 0) OnHandSpecializedPaint
, IsNull([Subassembly], 0) OnHandAssembly
, IsNull([Final Assembly], 0) OnHandFinalAssembly
, IsNull([Paint Storage], 0) + IsNull([Frame Forming], 0) + IsNull([Frame Welding], 0) + IsNull([Debur and Polish], 0) + IsNull([Specialized Paint], 0) + IsNull([Subassembly], 0) + IsNull([Final Assembly], 0) OnHand
, ListPrice
, StandardCost
, (IsNull([Paint Storage], 0) + IsNull([Frame Forming], 0) + IsNull([Frame Welding], 0) + IsNull([Debur and Polish], 0) + IsNull([Specialized Paint], 0) + IsNull([Subassembly], 0) + IsNull([Final Assembly], 0) ) * StandardCost Value
FROM
(
SELECT PP.Name
, PP.ProductNumber
, PP.MakeFlag
, PP.FinishedGoodsFlag
, PL.Name AS Location
, PP.ListPrice
, PP.StandardCost
, PPI.Quantity
FROM Production.ProductInventory PPI
JOIN Production.Product PP
ON PPI.ProductID = PP.ProductID
JOIN Production.Location PL
ON PPI.LocationID = PL.LocationID
) AS A
PIVOT
(
SUM(Quantity)
FOR Location IN ([Paint Storage], [Frame Forming], [Frame Welding], [Debur and Polish], [Specialized Paint], [Subassembly], [Final Assembly])
) PVT

    The query is not rocket science, but it took me some time to figure out that when I need to use multiple tables, I have to create first a subquery, and only then can apply the PIVOT operator. This is how I tried to write my query:
SELECT PP.Name
, PP.ProductNumber
, PP.MakeFlag
, PP.FinishedGoodsFlag
, IsNull([Paint Storage], 0) OnHandPaintStorage
, IsNull([Frame Forming], 0) OnHandFrameForming
, IsNull([Frame Welding], 0) OnHandFrameWelding
, IsNull([Debur and Polish], 0) OnHandDeburPolish
, IsNull([Specialized Paint], 0) OnHandSpecializedPaint
, IsNull([Subassembly], 0) OnHandAssembly
, IsNull([Final Assembly], 0) OnHandFinalAssembly
, IsNull([Paint Storage], 0) + IsNull([Frame Forming], 0) + IsNull([Frame Welding], 0) + IsNull([Debur and Polish], 0) + IsNull([Specialized Paint], 0) + IsNull([Subassembly], 0) + IsNull([Final Assembly], 0) OnHand
, PP.ListPrice
, PP.StandardCost
, (IsNull([Paint Storage], 0) + IsNull([Frame Forming], 0) + IsNull([Frame Welding], 0) + IsNull([Debur and Polish], 0) + IsNull([Specialized Paint], 0) + IsNull([Subassembly], 0) + IsNull([Final Assembly], 0) ) * StandardCost Value
FROM Production.ProductInventory PPI
JOIN Production.Product PP
ON PPI.ProductID = PP.ProductID
JOIN Production.Location PL
ON PPI.LocationID = PL.LocationID
PIVOT
(
SUM(Quantity)
FOR PL.Name IN ([Paint Storage], [Frame Forming], [Frame Welding], [Debur and Polish], [Specialized Paint], [Subassembly], [Final Assembly])
) PVT
and the error: Msg 8156, Level 16, State 1, Line 1
The column 'ProductID' was specified multiple times for 'PVT'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PP.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PP.ProductNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PP.MakeFlag" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PP.FinishedGoodsFlag" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PP.ListPrice" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PP.StandardCost" could not be bound.

Notes:
1. Probably it's better to create a view for subquery and use the view instead. I am doing that most of the times, sometimes I prefer to write table-valued functions; more on this in another post.
2. As it seems can be used only one aggregation function with PIVOT, that's pretty bad because even if you start doing your query using a PIVOT and then the requirements change (e.g. is needed to output how many records are for each Location, a more appropriate example is the ones of Purchase Orders, in which could be aggregations applied on Ordered, Receipt and Open Quantity) then you'll have to change the whole query. I'm expecting more from next implementations of PIVOT operator, I hope Microsoft will have that on their list.
3. The benefit of PIVOT operator is supposed to be that it allows writing shorter code, not necessarily. Of course, it allows to eliminate the redundant code - the multiple CASE WHEN statments, however this works when there is only one column (Location in our example) used to do the split, what if there is additional logic which must be included in CASEs?

No comments: