>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