sql - How to combine multiple columns into one column and its data into another column? -

i looked around first before posting , found lot of questions not solution looking for, question different...

i tasked normalize existing database , stuck when writing view thats transparent existing database, wrote this...

select      customer.customerid, customercontact.customercontactid,     customerphone.phone "1",     customerphone.work "2",     customerphone.mobile "3",     customerphone.fax "4",     customerphone.other "5",      customerspouse.spouse "6",      customerspouse.spousemobile "7"     [customer]  inner join      customercontact on customer.customerid = customercontact.customerid  inner join      customerphone on customercontact.customercontactid = customerphone.customercontactid  inner join      customerspouse on customercontact.customercontactid = customerspouse.customercontactid 

so made mistake normalization, in existing database, customerphone table looks this

now have no idea how put columns new column called phonetype , show phone number in column called phonenumber.

i rather not have go , change tables have done, last resort.

i apologize if how explained sounds confusing, if please let me know , i'll make edit , try explain better.

thanks

edit here diagram of tables

here have being returned query wrote

so need every column has integer header placed in column called phonetype , corresponding phone number column called phonenumber.

what need query making return this.

phonetype | phonenumber 1         | 123-4567 2         | 234-5678 3         | 345-6789 

i think want unpivot (see this question):

create table phones_wide (customerid int, "1" varchar(20), "2" varchar(20), "3" varchar(20), "4" varchar(20), "5" varchar(20), "6" varchar(20), "7" varchar(20)); insert phones_wide values (1234, '123-4342', '223-4342', '323-4342', '423-4342', '523-4342', '623-4342', '723-4342')  select phonetype, phonenumber phones_wide unpivot (   phonenumber   phonetype in ("1","2","3","4","5","6","7") ) u;  drop table phones_wide 

output:

phonetype   phonenumber 1   123-4342 2   223-4342 3   323-4342 4   423-4342 5   523-4342 6   623-4342 7   723-4342