CODEWITHSUNDEEP
sql-servert-sql
Pavel
Pavel

Reputation: 1288

Turn many to many relationship into single row

I have the following tables

Users

Id
FirstName
LastName

Sample Data

1,'Peter','Smith'
2,'John','Como'

Phones

Id
UserId
PhoneTypeId
Phone
ContactName

Sample data

1,1,4,'555-555-5551','Peter'
2,1,4,'555-555-5552','Paul'
3,1,4,'555-555-5553','Nancy'
4,1,4,'555-555-5554','Hellen'

PhoneTypes 

Id
Type

with sample data

1 Home
2 Work
3 Cell
4 Emergency

I have to create following result

UserId, UserFirstName, UserLastName, FirstEmergencyContactName, FirstEmergencyContactPhone, SecondEmergencyContactName, SecondEmergencyContactPhone, ThirdEmergencyContactName, ThirdEmergencyContactPhone, FourthEmergencyContactName, FourthEmergencyContactPhone, FifthEmergencyContactName, FifthEmergencyContactPhone

How can I create a single row for every user with emergency contacts? Some of the users might have one emergency contact and others might have many, but I need only five of them.

Upvotes: 0

Views: 82

Answers (2)

gofr1
gofr1

Reputation: 15977

Also you can use pivoting, without dynamic SQL and hard-coding, because you need only 5 contacts/phones. Example below:

;WITH cte AS (
SELECT  p.UserId, 
        FirstName, 
        LastName,
        CAST(ContactName as nvarchar(100)) as ContactName,
        CAST(Phone as nvarchar(100)) as ContactPhone,
        CAST(ROW_NUMBER() OVER (PARTITION BY p.UserId ORDER BY pt.Id) as nvarchar(100)) as RN
FROM Users u
INNER JOIN Phones p
    ON p.UserId = u.Id
INNER JOIN PhoneTypes pt
    ON pt.Id = p.PhoneTypeId
WHERE pt.Id = 4
)

SELECT *
FROM (
    SELECT  UserId, 
            FirstName, 
            LastName,
            [Columns]+RN as [Columns],
            [Values]
    FROM cte
    UNPIVOT (
        [Values] FOR [Columns] IN (ContactName, ContactPhone)
    ) as unp
) as t
PIVOT (
    MAX([Values]) FOR [Columns] IN (ContactName1,ContactPhone1,ContactName2,ContactPhone2,ContactName3,ContactPhone3,
ContactName4,ContactPhone4,ContactName5,ContactPhone5)
) as pvt

Output:

UserId  FirstName   LastName    ContactName1    ContactPhone1   ContactName2    ContactPhone2   ContactName3    ContactPhone3   ContactName4    ContactPhone4   ContactName5    ContactPhone5
1       Peter       Smith       Peter           555-555-5551    Paul            555-555-5552    Nancy           555-555-5553    Hellen          555-555-5554    NULL            NULL
2       John        Cono        Harry           555-555-5555    William         555-555-5556    John            555-555-5557    NULL            NULL            NULL            NULL

I add some more contacts.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

This is called table pivoting. Since you want no more than 5 results, you can use conditional aggregation with row_number:

select id, firstname, lastname,
       max(case when rn = 1 then contactname end) emergency_contact1,
       max(case when rn = 1 then phone end) emergency_phone1,
       max(case when rn = 2 then contactname end) emergency_contact2,
       max(case when rn = 2 then phone end) emergency_phone2,
       ...
from (
    select u.id, u.firstname, u.lastname, p.phone, p.contactname, 
           row_number() over (partition by u.id order by p.phonetypeid) rn
    from users u 
       join phones p on u.id = p.userid
) t
group by id, firstname, lastname

Upvotes: 1

Related Questions