SQL SERVER PIVOT QUERY - 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)

SQL SERVER PIVOT QUERY

Author

Topic

Andy878
Starting Member

1 Post

Posted - 2009-12-22 : 22:09:07

Hi Guys,

Need help with a pivot query.

My query after joining couple of tables is like this

ID NAME PLACE DATE
001 A Sydney 1-Jun-2009
001 A Macau 2-Jun-2009
001 A Texas 3-Jun-2009

I want it to be displayed like this

ID NAME SYDNEY MACAU TEXAS
001 A 1-Jun-2009 2-Jun-2009 3-Jun-2009

Please help!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-22 : 22:46:35

check out http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-23 : 01:54:41

select id,name,
case when place ='sydney' then date end sydney,
case when place ='macau' then date end macau,
case when place ='texas' then date end texas
from tablename

select id,name,
[sydney],[macau],[texas] from tablename
pivot (max(date) for name in ([sydney],[macau],[texas]))pvt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 01:57:23

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources