By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

SQL Server 2005 Pivot Table

P: 62
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:

Expand|Select|Wrap|Line Numbers
  1. BEGIN
  2.     -- SET NOCOUNT ON added to prevent extra result sets from
  3.     -- interfering with SELECT statements.
  4.     SET NOCOUNT ON;
  5.  
  6.     SELECT [TIMESTAMP], [1] AS Green, [2] AS Yellow, [3] AS Red
  7.     FROM 
  8.      (SELECT [TIMESTAMP], GYR_ID, RCOUNT
  9.         FROM dbo.HISTORIC_STOPLIGHT
  10.         WHERE PROGRAM=@PROGRAM) AS p
  11.     PIVOT
  12.     (
  13.         SUM(RCOUNT) FOR [TIMESTAMP] IN ( [1], [2], [3] )
  14.     ) AS pvt
  15.     ORDER BY [TIMESTAMP]
  16. END
I am getting errors on the line 13 with
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 15 '08 #1
Share this Question
Share on Google+
1 Reply


P: 62
I figured out my problem. I was using the wrong Pivot arguments. The correct code for what I was trying to do is:
Expand|Select|Wrap|Line Numbers
  1.     SELECT [TIMESTAMP], [1] AS Green, [2] AS Yellow, [3] AS Red
  2.     FROM 
  3.      (SELECT [TIMESTAMP], GYR_ID, RCOUNT
  4.         FROM dbo.HISTORIC_STOPLIGHT
  5.         WHERE PROGRAM=@PROGRAM) AS p
  6.     PIVOT
  7.     (
  8.         MAX(RCOUNT) FOR [GYR_ID] IN ( [1], [2], [3] )
  9.     ) AS pvt
  10.     ORDER BY [TIMESTAMP]
Thanks anyway. Perhaps this will help someone else down the road.

Nickvans
Oct 15 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.