SQL Reloaded: 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?