Eduard Climov Eduard Climov - 2 months ago 6
SQL Question

Selecting row data as columns

I have a contacts table where contact_type_id means:

1- email
2 - phone
3 - skype

The example below shows 3 different users with different types of contacts.

First user has phone number and Skype. The second one has only email. The third one has all 3 types: email, phone number & Skype.

user_id contact_type_id value
1 2 353234
1 3 skypeLogin
2 1 example@mail.com
3 1 example2@mail.com
3 2 123345
3 3 skypeLogin2


Issue needed to be clarified
How can I select this data as the following table

user_id email phone skype
1 null 353234 skypeLogin
2 example@mail.com null null
3 example2@mail.com 123345 skypeLogin2

Answer

Pivot is what you're looking for. If you have an unknown number of contact_type_id's then google Dynamic Pivot - there are tons of examples on SO.

This is for SQL Server:

CREATE TABLE #Contacts
       (
        user_id INTEGER
       ,contact_type_id INTEGER
       ,value NVARCHAR(20)
       );

INSERT INTO #Contacts
        ( user_id, contact_type_id, value )
    VALUES
        ( 1, 2, '353234' ),
        ( 1, 3, 'skypeLogin' ),
        ( 2, 1, 'example@mail.com' ),
        ( 3, 1, 'example2@mail.com' ),
        ( 3, 2, '123345' ),
        ( 3, 3, 'skypeLogin2' );


SELECT
        pvt.user_id
       ,pvt.[1] email
       ,pvt.[2] phone
       ,pvt.[3] skype
    FROM
        #Contacts 
    PIVOT( MAX(value) FOR contact_type_id IN ( [1], [2], [3] ) ) pvt;



user_id     email                phone                skype
----------- -------------------- -------------------- --------------------
1           NULL                 353234               skypeLogin
2           example@mail.com     NULL                 NULL
3           example2@mail.com    123345               skypeLogin2

(3 row(s) affected)