Simple SQL pivot

Question

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, whereas I have two, it's doing a rank partition which I don't know is necessary, I can't actually get it to work, plus it's 5 years old and I'm hoping there's an easier way.

I am sure this is a duplicate question so if someone can find it then please do!

People table:

PersonID
========
   1
   2
   3

Device table:

DeviceID | PersonID
===================
  1111        1
  2222        1
  3333        1
   123        2
   456        2
  9999        3

I do a join like this:

SELECT p.PersonID, d.DeviceID FROM People p
    LEFT JOIN Device d on d.PersonID = p.PersonID

Which gives me:

PersonID | DeviceID 
===================
  1         1111
  1         2222
  1         3333
  2          123
  2          456
  3         9999

I know what you're thinking, it's just the Device table, but this is a minimal version of the query and tables, there's much more going on in the real ones,

I want to be able to inject a join on the People table to the Device table and get three columns:

Must I use PIVOT to get the results like this? (there will always be a max of three devices per person)

PersonID |     1     |     2      |     3
===============================================
    1         1111        2222         3333
    2          123         456
    3         9999

(Where the blanks would be NULL)

I'm trying:

SELECT PersonID, [1], [2], [3]
FROM (
    SELECT p.PersonID, d.DeviceID FROM People p
    LEFT JOIN Device d on d.PersonID = p.PersonID) AS r
PIVOT
(
  MAX(DeviceID)
  FOR DeviceID IN([1], [2], [3])
) AS p;

But it's giving me NULL for all three columns.

You can do a pivot on the subquery; or am I missing something. — Peter Smith, Jul 24 '20 at 07:39
I've just added what I tried for pivoting on the original query, but it's giving me NULL results. I'm trying to sub the column names for the MAX() and FOR parts but it gives me syntax errors. — jamheadart, Jul 24 '20 at 07:49
The alternative to Pivot would be using Case statements. Example here. [link](https://stackoverflow.com/a/5846029/2913389) — Yarner, Jul 24 '20 at 08:00
@Yarner not sure how I could use case statements when dealing with unique IDs from both tables — jamheadart, Jul 24 '20 at 08:41

Sander · Accepted Answer · 2020-07-24T08:07:11.837

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 @person table
(
    personid int,
    personname nvarchar(100)
);

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

declare @device table
(
    personid int,
    deviceid int
);

insert into @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.

with base as
(
    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
)
select piv.personname, piv.DEVICE_1, piv.DEVICE_2, piv.DEVICE_3
from base
pivot( max(deviceid) for key_ in ([DEVICE_1], [DEVICE_2], [DEVICE_3]) ) piv;

Result

The intermediate CTE result table

personname deviceid    key_
---------- ----------- ----------
Ann        1111        DEVICE_1                             
Ann        2222        DEVICE_2                             
Ann        3333        DEVICE_3                             
Britt      123         DEVICE_1                             
Britt      456         DEVICE_2                             
Cedric     9999        DEVICE_1

The final result

personname DEVICE_1    DEVICE_2    DEVICE_3
---------- ----------- ----------- -----------
Ann        1111        2222        3333
Britt      123         456         NULL
Cedric     9999        NULL        NULL

That's fantastic, works great to get my pivoted info, but using a CTE like this I don't think it'd be possible to inject this to an existing query that has lots of joins on it already? — jamheadart, Jul 24 '20 at 08:37
Actually never mind, I was able to rewrite it with nested queries rather than using a CTE, thanks again! — jamheadart, Jul 24 '20 at 08:45

Simple SQL pivot

1 Answers1

Linked