Transpose Rows to columns based on key
-
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!
-
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
Jay Madren -
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!
-
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
Jay Madren -
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
.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply