SQL server database merging two columns?

I have a table emp as follows:

empid  empname homephone  homeadd   workphone workadd
1      ron     209999000  1234st    222222    unit-20c
2      dd      2222333    unit56c   3444      bloor

I would like to create a temp table as follows:

empid  empname phone      add
1      ron     209999000  1234st
1      ron     222222     unit-20c
2      dd      2222333    unit56c
2      dd      3444       bloor

I would like to merge homephone and workphone under column phone and merge homeadd and workadd开发者_JS百科 under column add. Anyone knows query for this?

You can do a union to get them all merged, if you know the number of columns you want to 'merge'

select empID, empName, Homephone, Homeaddr from temptable
union
select empID, empName, WorkPhone, WorkAddr from temptable
union
select empID, empName, ExtraPhone, ExtraAddress from temptable

Merge how? There's coalesce() and concat() - coalesce will return the first non-null argument, and concat will return all its fields as a single string. What do you want? The first address/phone found, or ALL the phones/addresses?

ok, thinking about it:

select empid, empname, homephone as phone, homeadd as add
from yourtable

union

select empid, empname, workphone as phone, workadd as add
from yourtable

not pretty, but it'll return what you want.