Help with pivot code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with pivot code

Author

Topic

shirotakakah
Starting Member

3 Posts

Posted - 2010-10-13 : 17:45:40

Hello All:

I have this code:

Select Code, Year, Count(Year) as Response
From tblEmployeeSurvey
Group By Code, Year
Order By Code, Year

And it produces:
Code Year Response
NULL 2008 27
NULL 2010 17
4.0 2008 1
4.0 2010 2
4.1.1 2010 1
4.1.10 2008 5
4.1.10 2010 1
4.1.4 2008 3
4.1.4 2010 2
4.1.9 2010 1
4.12.9 2008 4
4.12.9 2010 12

I want my output data to look like this:
Code 2008 2010
NULL 27 17
4.0 1 2
4.1.1 NULL 1
4.1.10 5 1
4.1.4 3 2
4.1.9 NULL 1
4.12.9 4 12

This is the code I am trying to use:

Select Code, [2008] , [2010]
FROM
(Select [Year], Code
From tblEmployeeSurvey
) as ST
PIVOT
(
count(Code)
FOR
[Year] in ([2008],[2010])
) AS PT

And I get the following Error:
Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'PIVOT'.

I'm just not familiar enough with this to get it to work.
Anyone able to whip this out real quick?

Thanks.

Kent.

matty
Posting Yak Master

161 Posts

Posted - 2010-10-14 : 01:28:28

[code]
SELECT
Code, [2008] , [2010]
FROM
(
SELECT [Year], Code,Response
FROM tblEmployeeSurvey
) AS ST
PIVOT
(
MAX(Response)
FOR
[Year] IN ([2008],[2010])
) AS PT
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 02:48:37

will the year values be static? ie. 2008,2010 always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shirotakakah
Starting Member

3 Posts

Posted - 2010-10-18 : 11:35:24

No, I think it could ultimately have other years in there, but it's a pretty slow mover, next update not till 2012. I wouldn't mind if it were static.

Thanks.

quote:
Originally posted by visakh16

will the year values be static? ie. 2008,2010 always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources