CODEWITHSUNDEEP
sqlsql-serverpivot
Molloch
Molloch

Reputation: 2381

PIVOT Table with Duplicate column key

I have a table, called PhoneNumber, which has:

CREATE TABLE PhoneNumber(
PhoneNumberID int Identity(1, 1),
CustomerID int NOT NULL,
Preference int NULL,
PhoneNumber nvarchar(20))

The customer might have 0-many phone numbers, I need to PIVOT this and join it to a view which outputs data for a mail merge. My user wants to be able to get merge data using the field names: Phone1, Phone2, Phone3, which come from the above table, and are ordered by preference. I only want the first 3 numbers ordered by preference.

The data is not pretty. The preference field may not be sequential, and is not unique for each customer. The customer might have 2 numbers with preference = 0 for instance. I don't really care which of the phone numbers is first if the preference is equal for more than one number, but I can't use a clean "Preference" as a column key as I would in a Dynamic PIVOT.

I need to join to this view on CustomerID. The view needs to have 4 columns: CustomerID, Phone1, Phone2, Phone3

I have tried using ROWNUMBER to assign a row number when ordered by preference, this doesn't work as it assigns a ROWNUMBER to each row in the PhoneNumber table.

I also tried a hideous UNION query but it was awful.

INSERT INTO PhoneNumber 
(CustomerID, Preference, PhoneNumber ) VALUES
(1, 0, '555-1234')
GO

INSERT INTO PhoneNumber 
(CustomerID, Preference, PhoneNumber ) VALUES
(1, 0, '555-4321')
GO

INSERT INTO PhoneNumber 
(CustomerID, Preference, PhoneNumber ) VALUES
(1, 4, '555-1111')
GO

INSERT INTO PhoneNumber 
(CustomerID, Preference, PhoneNumber ) VALUES
(2, 0, '555-2222')
GO

Output

CustomerID | Phone1    | Phone2    | Phone3    
1          | 555-1234  | 555-4321  | 555-1111
2          | 555-2222  | NULL      | NULL

Upvotes: 0

Views: 2016

Answers (1)

Pradeeshnarayan
Pradeeshnarayan

Reputation: 1235

SELECT * FROM(
    SELECT CustomerID,r_id,PhoneNumber FROM (
        SELECT * ,ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY preference) as r_id
        FROM PhoneNumber ) AS a WHERE r_id<=3
    )AS P
PIVOT(
    MAX(PhoneNumber) FOR r_id in ([1],[2],[3])
)AS Pvt

Hope this will help you.

Upvotes: 2

Related Questions