Complex Pivot syntax 1

[Luvsql]

I'm not sure if the Pivot clause can do this but thought I'd throw a question out before I pull my hair out.

Table

Location ItemCode Qty
CAL ABC 2
HAL ABC 3
REG ABC 1
CAL XYZ 5
REG XYZ 4 (notice htat HAL does not have qty of XYZ)

I would like a simple result set with the following columns: ItemCode, QtyCAL, QtyHAL, QTYREG

Results:

ItemCode, QtyCAL, QtyHAL, QTYREG
ABC 2 3 1
XYZ 5 NULL 4


ABC

 

[gmmastros]

Select  ItemCode,
        MIN(Case When Location = 'CAL' Then Qty End) As QtyCAL,
        MIN(Case When Location = 'HAL' Then Qty End) As QtyHAL,
        MIN(Case When Location = 'REG' Then Qty End) As QtyREG
From    YourTableNameHere
Group By ItemCode

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Luvsql]

Brilliant no Pivot needed!