PIVOT without Aggregate ? - 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

PIVOT without Aggregate ?

Author

Topic

scgibson
Starting Member

2 Posts

Posted - 2010-07-19 : 08:29:23

I have the following table of data

ID Code Value
1 118 G
1 198 2
2 118 1
2 198 x

What I need is:

ID 118 198
1 G 2
2 1 x

But I cant seem to be able to use PIVOT as it requires an aggregate value but my column is VARCHAR.

Any ideas or is PICOT not what I should be using?

I am new to SQL so be kind....

thanks,

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-19 : 08:48:30

select *
from (
SELECT id
,code
,value
FROM T1
) as D
PIVOT
(
max(value)
For Code in
([118],[198])
) as P

I have a blog article to maybe assist you with your question:

http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-19 : 08:53:36

This is for dynamic values
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

scgibson
Starting Member

2 Posts

Posted - 2010-07-19 : 09:10:30

Thanks for the fast response - simple when you know how. Added links to boookmarks.

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources