>>Hi All -
>>
>>Once again I find myself needing some help with PIVOT/UNPIVOT.
>>I have a table containing traffic classification counts with columns and data that look like this.
>>
>>Classification, P1, P2, P3, P4, P5, . . .
>>1, 0 0 0 0 0
>>2 , 1 0 0 1 2
>>3,
>>4, etc.
>>5,
>>6,
>>
>>The six rows constitute a single traffic count. The classifications represent the various types of vehicle, car, motorcycle, truck, etc.
>>The P fields represent the different time periods throughout the day.
>>
>>I want to select the six records but pivot the result on Classification so that the result looks like this.
>>
>>Classification, 1, 2, 3, 4, 5, 6
>>P1, 0 1
>>P2, 0 0
>>P3, 0 0 etc.
>>P4, 0 1
>>P5, 0 2
>>.
>>.
>>.
>>P196
>>
>>There is no aggregate involved so I think the correct command choice is UNPIVOT (but I am not sure). I have written this code which works to a point.
>>

>>SELECT TimePeriod, 
>>(CASE WHEN classification=1 THEN VehicleCount END) as 'Class1',
>>(CASE WHEN classification=2 THEN VehicleCount END) as 'Class2',
>>(CASE WHEN classification=3 THEN VehicleCount END) as 'Class3',
>> (CASE WHEN classification=4 THEN VehicleCount END) as 'Class4',
>> (CASE WHEN classification=5 THEN VehicleCount END) as 'Class5',
>> (CASE WHEN classification=6 THEN VehicleCount END) as 'Class6',
>>VehicleCount
>>FROM 
>>   (SELECT classification, p1, p2, p3, p4, p5
>>   FROM ClassCounts WHERE LocID='N-18') p
>>UNPIVOT
>>   (VehicleCount FOR TimePeriod IN 
>>      (p1, p2, p3, p4, p5)
>>)AS unpvt;
>>

>>
>>But the classifications are staggered and filled with nulls. IF I am on the right track I need a way to collapse the multiple 'P' records so that each one only appears once. I hope that makes sense. I tried working COALESCE but was not successful. As always, any help is appreciated.
>>
>>Thanks
>
>Try:
>

>;with cteUnpvt AS (SELECT * FROM
>   (SELECT classification, p1, p2, p3, p4, p5
>   FROM ClassCounts WHERE LocID='N-18') p
>UNPIVOT
>   (VehicleCount FOR [TimePeriod] IN 
>      (p1, p2, p3, p4, p5)
>)AS unpvt)
>
>select * FROM cteUnpvt PIVOT (max(VehicleCount) FOR Classification IN ([1],[2],[3],[4],[5],[6])) pvt
>

Beautiful!
Thanks Naomi, I wish I understood it more.