SQL Server 2005 Pivot Table

[nickvans]

Hello generous SQL experts!

I'm new to the SQL Server environment, but am trying to get up to speed. I am trying to help a coworker create a pivot table for use with an Infragistics chart. I have data in the form of:

[Timestamp] | GYR_ID | RCOUNT

[Timestamp] is a smalldatetime
GYR_ID is an int which represents a category (1=green, 2=yellow, 3=red)
RCOUNT is how many of that category are present

For example at [TIMESTAMP] = 10/13/2008 12:32 PM there are three records

[Timestamp] , GYR_ID , RCOUNT
10/13/2008 12:32:00 PM , 1, 4
10/13/2008 12:32:00 PM , 2, 5
10/13/2008 12:32:00 PM , 3, 6

The above indicates that there are 4 green, 5 yellow and 3 red records.


I need to pivot this data so I have an output of

[Timestamp] | Green | Yellow | Red
10/13/2008 12:32:00 PM, 4 5 6

I'm having difficulty troubleshooting my pivot stored procedure. This is what I have:

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    SELECT [TIMESTAMP], [1] AS Green, [2] AS Yellow, [3] AS Red
    FROM 
     (SELECT [TIMESTAMP], GYR_ID, RCOUNT
        FROM dbo.HISTORIC_STOPLIGHT
        WHERE PROGRAM=@PROGRAM) AS p
    PIVOT
    (
        SUM(RCOUNT) FOR [TIMESTAMP] IN ( [1], [2], [3] )
    ) AS pvt
    ORDER BY [TIMESTAMP]
END

I am getting errors on the line 13 with

SUM(RCOUNT) FOR [TIMESTAMP] IN( [1],[2],[3] )

The error messages read:
1. Error converting data type nvarchar to smalldatetime.
2. The incorrect value "1" is supplied in the PIVOT operator.
3. Invalid column name 'TIMESTAMP'.

Any help/advise you can give me would be great.

Thanks in advance!

Nickvans

[nickvans]

I figured out my problem. I was using the wrong Pivot arguments. The correct code for what I was trying to do is:

    SELECT [TIMESTAMP], [1] AS Green, [2] AS Yellow, [3] AS Red
    FROM 
     (SELECT [TIMESTAMP], GYR_ID, RCOUNT
        FROM dbo.HISTORIC_STOPLIGHT
        WHERE PROGRAM=@PROGRAM) AS p
    PIVOT
    (
        MAX(RCOUNT) FOR [GYR_ID] IN ( [1], [2], [3] )
    ) AS pvt
    ORDER BY [TIMESTAMP]

Thanks anyway. Perhaps this will help someone else down the road.

Nickvans