Simple Sql Pivot

Googling SQL PIVOT brings up answers to more complex situations than I need with aggregations, and although I did find this simple SQL Pivot Query , it's pivoting on a single table

Solution 1:

The value list defined in the pivot clause must contain actual values from your table. [1], [2], [3] are values from your PersonId, not for DeviceId. So the part for DeviceId in [1], [2], [3] is not producing any results, hence all the null values.

Here is my solution. I constructed a new key_ column to pivot around.

Sample data with added person names

declare@persontable
(
    personid int,
    personname nvarchar(100)
);

insertinto@person (personid, personname) values
(1, 'Ann'),
(2, 'Britt'),
(3, 'Cedric');

declare@devicetable
(
    personid int,
    deviceid int
);

insertinto@device (personid, deviceid) values
(1, 1111),
(1, 2222),
(1, 3333),
(2,  123),
(2,  456),
(3, 9999);

Solution

Run the CTE part on its own to see the intermediate result table. The key_ column contains values like DEVICE_* which are the same values used in the for key_ in part of the pivot clause.

withbaseas
(
    select  p.personname,
            d.deviceid,
            'DEVICE_' + convert(char, ROW_NUMBER() over(partition by p.personname order by d.deviceid)) as 'key_'
    from @person p
    join @device d
        on d.personid = p.personid
)
selectpiv.personname, piv.DEVICE_1, piv.DEVICE_2, piv.DEVICE_3frombasepivot( max(deviceid) for key_ in ([DEVICE_1], [DEVICE_2], [DEVICE_3]) ) piv;

Result

The intermediate CTE result table

personnamedeviceidkey_-------------------------------Ann1111        DEVICE_1Ann2222        DEVICE_2Ann3333        DEVICE_3Britt123DEVICE_1Britt456DEVICE_2Cedric9999        DEVICE_1

The final result

personnameDEVICE_1DEVICE_2DEVICE_3-------------------------------------------Ann1111        2222        3333Britt123456NULLCedric9999        NULLNULL