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