0

I looked around first before posting this and found a lot of questions like this but not the solution I was looking for, this question is different...

I was tasked to normalize an existing database and now I am stuck when writing a view thats to be transparent to the existing database, so I wrote this...

SELECT 
    Customer.CustomerID, CustomerContact.CustomerContactID,
    CustomerPhone.Phone AS "1",
    CustomerPhone.Work AS "2",
    CustomerPhone.Mobile AS "3",
    CustomerPhone.Fax AS "4",
    CustomerPhone.Other AS "5", 
    CustomerSpouse.Spouse AS "6", 
    CustomerSpouse.SpouseMobile AS "7"
FROM
    [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 I made a mistake with the normalization, in the existing database, the CustomerPhone table looks like this

Existing database table

Now I have no idea how to put all the columns into a new column called PhoneType and show the phone number in another column called PhoneNumber.

I would rather not have to go back and change the tables from what I already have done, that is a last resort.

I apologize if how I explained it sounds confusing, if it does then please let me know and i'll make an edit and try to explain it better.

Thanks

EDIT Here is the diagram of the tables

Diagram

Here is what I have being returned with the query that I wrote

What is being returned with my query

So I need every column that has an integer for a header to be placed in a column called PhoneType and its corresponding phone number into a column called PhoneNumber.

What I need to do with my query is making it return this.

PhoneType | PhoneNumber
1         | 123-4567
2         | 234-5678
3         | 345-6789
Chris
  • 2,953
  • 10
  • 48
  • 118

1 Answers1

1

I think you want unpivot (see also 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 into phones_wide values (1234, '123-4342', '223-4342', '323-4342', '423-4342', '523-4342', '623-4342', '723-4342')

select PhoneType, PhoneNumber
from phones_wide
unpivot
(
  PhoneNumber
  for 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
Community
  • 1
  • 1
maxymoo
  • 35,286
  • 11
  • 92
  • 119
  • I tried modifying what you suggested, and I took a look at the link you provided, this would work if my table looked like the pic that I attached. Maybe I am doing something wrong? – Chris Jun 04 '15 at 04:00
  • Can you post an excerpt of your 'normalized' table? – maxymoo Jun 04 '15 at 04:02
  • Sure, its a combination of 4 tables altogether, I tried logging into SQLFiddle, but it wouldn't let me. One sec.. – Chris Jun 04 '15 at 04:03
  • What error are you getting? I've added an example table into my code, and it seems to be working for me. – maxymoo Jun 04 '15 at 05:09
  • Not getting any errors now. I seen why I was getting errors, I needed to put my select statement as a sub query and give it an alias. Thanks for getting this straightened out for me I appreciate it. – Chris Jun 04 '15 at 05:32