Transpose Table in 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)

Transpose Table in Query

Author

Topic

EarlXT
Starting Member

10 Posts

Posted - 2009-11-24 : 12:04:27

I have three tables as follows

Location Table

ID Location
1 Calgary
2 Toronto
3 Vancouver

Device Table

ID Name
1 router
2 radio
3 server

Status Table

ID LocationID DeviceID IsActive
1 1 1 True
2 1 2 True
3 1 3 False
4 2 1 True
5 2 2 True
6 2 3 True
7 3 1 True
8 3 2 False
9 3 3 True

I want to display the IsActive Status for each device by location so that it looks like the following:

Desired Query Results

LocationID Router Radio Server
1 True True False
2 True True True
3 True False True

Has someone created the pattern to do this or do I need to figure it out all on my own?

Thanks for your help..

Earl

EarlXT

EarlXT
Starting Member

10 Posts

Posted - 2009-11-24 : 14:05:05

Here is a simplified version of the problem

The existing table looks like this

XX YY ZZ
1 A M
1 B N
1 C O
2 A P
2 B Q
2 C R
3 A S
3 B T
3 C U

and I want to create a query to tranpose the table to this:

XX A B C
1 M N O
2 P Q R
3 S T U

Again, thanks for any help. Earl

EarlXT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-24 : 15:12:19

Here's one way for sql 2005 and later:


with yourTable (xx,yy,zz)
as (
select 1, 'A', 'M'
union all select 1, 'B', 'N'
union all select 1, 'C', 'O'
union all select 2, 'A', 'P'
union all select 2, 'B', 'Q'
union all select 2, 'C', 'R'
union all select 3, 'A', 'S'
union all select 3, 'B', 'T'
union all select 3, 'C', 'U'
)

select * 
from yourTable
pivot (max(zz) for yy in ([A], [B], [C])) p

output:
xx          A    B    C
----------- ---- ---- ----
1           M    N    O
2           P    Q    R
3           S    T    U

Be One with the Optimizer
TG

EarlXT
Starting Member

10 Posts

Posted - 2009-11-24 : 18:08:08

TG, Thankyou!!! I have started to read more about pivot tables since it provided such an elegent solution.

EarlXT

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 01:48:51

If the values are dynamic,
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