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
