Another ROWS to COLUMNS - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Another ROWS to COLUMNS

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2009-10-26 : 19:18:45

Hi, I have a query that generates the following result set.

Name Year Mark
Dave 2004 87.00
John 2004 24.00
Dave 2005 55.00
John 2005 13.00
Dave 2006 86.00
John 2006 23.00

I'd like to display this data as:

Year Dave John
2004 87.00 24.00
2005 55.00 13.00
2006 86.00 23.00

I don't need to summarize any day, just rework the columns/rows as above. I was looking through the related posts but nothing seemed to suit. Any help much appreciated.

Thanks,
LW

singularity
Posting Yak Master

153 Posts

Posted - 2009-10-26 : 19:24:22

[code]
select [year], sum(case when name = 'Dave' then mark else 0 end) as Dave,
sum(case when name = 'John' then mark else 0 end) as John
from yourtable
group by [year]
[/code]

littlewing
Starting Member

33 Posts

Posted - 2009-10-26 : 19:38:17

Ok works nicely for my (poor) example, but 'Dave' and 'John' are just sample names, there can be many of these so I can't really CASE each one - can we dynamically derive the CASE statements one per distinct name?

Thank you.
LW

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-27 : 00:21:11

Yes. See the dynamic pivoting example by Madhivanan
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources