Use column names as values for Phone Number

Question

I have a table in which has phone numbers split out into separate columns cellphone, homephone, workphone and otherphone. I would like to do some sort of transformation which pulls all the phones into one column but shows the phone relationship in the next column so currently I have data that looks like this:

CellPhone  HomePhone  workPhone   OtherPhone
8756785468 8756944858 8756944903  8937944858

What I would like to see:

TelephoneNumber  TelephoneRelationship
8756785468       Cell
8756944858       Home
8756944903       Work
8937944858       Other

My create statement is rather simple:

Create Table ods.Telephone(
    CellPhoneNumber varchar(10) null,
    HomePhoneNumber varchar(10) null,
    WorkPhoneNumber varchar(10) null,
    OtherPhoneNumber Varchar(10) null
);

Answer 1

Just one more option via a CROSS APPLY

Select A.ID
      ,B.*
 From  Telephone A
 Cross Apply ( values (CellPhoneNumber,'Cell')
                     ,(HomePhoneNumber,'Home')
                     ,(WorkPhoneNumber,'Work')
                     ,(OtherPhoneNumber,'Other')
             ) B(TelephoneNumber,TelephoneRelationship)

Returns

ID  TelephoneNumber TelephoneRelationship
1   8756785468      Cell
1   8756944858      Home
1   8756944903      Work
1   8937944858      Other