From Long to Wide - 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 2000 Forums

SQL Server Development (2000)

From Long to Wide

Author

Topic

lee_h
Starting Member

36 Posts

Posted - 2003-04-02 : 00:44:39

I will show much appreciation to anyone that can help me with this query.

I'm trying to pivot some data from one long table into a wide table,

want to move this:

Table B
t c v
---- ----------- -----------
A 1 123
A 2 234
A 3 455
B 1 234
B 2 863
B 3 737

into this:

Table A
t t1 t2 t3
---- ----------- ----------- -----------
A 123 234 455
B 234 863 737

This is how I went about it

create table #a(t char(1), t1 int, t2 int, t3 int)

create table #b(t char(1), c int, v int)

insert into #a(t)
select 'A'
union select 'B'

insert into #b (t,c,v)
select 'A', 1, 123
union select 'A', 2, 234
union select 'A', 3, 455
union select 'B', 1, 234
union select 'B', 2, 863
union select 'B', 3, 737

update a
set
t1 = case c when 1 then v else t1 end,
t2 = case c when 2 then v else t2 end,
t3 = case c when 3 then v else t3 end
from #a a inner join #b b on a.t=b.t

however my table A looked like this:

t t1 t2 t3
---- ----------- ----------- -----------
A NULL NULL 455
B NULL NULL 737

This is a sample of a bigger query, so I don't really want to use 3 queries to do it.
Assuming this didn't wor, because t1 is null to start with, so updating it back to itself will just set it to null again.

There is probably something blidingly obvious, but I've been staring at it for so long, that I can't remeber how to spell remember!!!

lee_h
Starting Member

36 Posts

Posted - 2003-04-02 : 01:02:54

I think the blindingly obvious has just struck me

update a
set
t1 = b.t1,
t2 = b.t2,
t3 = b.t3
from #a a
inner join
(select t,
max(case c when 1 then v end) t1,
max(case c when 2 then v end) t2,
max(case c when 3 then v end) t3
from #b
group by t
) b
on a.t=b.t

lee_h
Starting Member

36 Posts

Posted - 2003-04-02 : 01:04:24

Thanks for your time Lee, very much appreciated

Have a warm flat beer on me.

Oh how I yearn for the warm flat beer of the mother land....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources