Transpose Rows to columns based on key

Question

Transpose Rows to columns based on key

rachel

SSCommitted

Points: 1626
More actions
May 2, 2003 at 9:53 am

#159496

I need a fast way populate table1 columns from table 2 rows. Table1 and table2 join on a key value.
ex.
Table1:
keyvalue
phone1
phone2
phone3
phone4
phone5
Table2:
keyvalue
addtlphone
Table 2 can have multiple key values that are the same, each with differet additional phones. I need to get all the additional phone numbers from table2 into the phone1-5 columns in table 1, matching on key value.
Does anyone have any good ideas besides cursors? I have a cursor that will do this, but it takes hours.
Thanks for your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jmadren SSCrazy Points: 2055 More actions · Answer 1

I haven't tested this, but something along these lines should work:



Update Table1 Set phone1 = min(addtlphone)

from Table1 inner join Table2 on Table1.keyvalue=Table2.keyvalue



Update Table1 Set phone2 = min(addtlphone)

from Table1 inner join Table2 on Table1.keyvalue=Table2.keyvalue

where addtlphone > phone1



Update Table1 Set phone3 = min(addtlphone)

from Table1 inner join Table2 on Table1.keyvalue=Table2.keyvalue

where addtlphone > phone2



...etc for the rest of them

Jay Madren

rachel SSCommitted Points: 1626 More actions · Answer 2

Can't have an aggregate in an update statement. I'm trying to think if a self join with the inner join would work but I'm not getting it yet.

Suggestions? Thanks!

jmadren SSCrazy Points: 2055 More actions · Answer 3

Sorry, I forgot about that. Like I said, I didn't test it .

This will work, as I did test it:



Update Table1 Set phone2 = phone

from Table1 inner join (Select Table2.keyvalue, min(addtlphone) phone

from Table1 inner join Table2 on Table1.keyvalue=Table2.keyvalue

where addtlphone > phone1 group by Table2.keyvalue) T on Table1.keyvalue=T.keyvalue



Update Table1 Set phone3 = phone

from Table1 inner join (Select Table2.keyvalue, min(addtlphone) phone

from Table1 inner join Table2 on Table1.keyvalue=Table2.keyvalue

where addtlphone > phone2 group by Table2.keyvalue) T on Table1.keyvalue=T.keyvalue



...etc.

Jay Madren

rachel SSCommitted Points: 1626 More actions · Answer 4

Thanks! I used a variation on your first idea that does work b/c it eliminates the aggregate. It probably wouldn't be a great solution if I had more than 4 fields to populate, but since its small its doable. You helped me get a 2 hour proc down to 2 minutes - thanks!! If you're interested, here's what I did...

update table1 set phone2 = addtlphone1 from table1 inner join tabl2 on

table1.keyvalue = table2.keyvalue and addtlphone1 is not null

update table1 set phone3 = addtlphone1 from table1 inner join table2 on

table1.keyvalue = table2.keyvalue and phone1 is not null

and addtlphone1 <> phone2

update table1 set phone4 = addtlphone1 from table1 inner join table2 on

table1.keyvalue = table2.keyvalue and phone1 is not null

and addtlphone1 <> phone2 and addtlphone1 <> phone3

...etc