SQL show extra row informatrion as columns

Question

Consider the following table:

User   | Device
-------+-----------
User 1 | Device 1
User 2 | Device 2
User 3 | Device 1
User 4 | Device 3

How can I get the users for, in this example Device 1 each in a separate column?

Device 1 | User 1 | User 3
Device 2 | User 2 |
Device 3 | User 4 |

There can be more users per device than 2; in fact the max number is unknown

Answer 1

You can use row_number() and conditional aggregation:

select
    device,
    max(case when rn = 1 then user end) user1,
    max(case when rn = 2 then user end) user2,
    ...
from (
    select t.*, row_number() over(partition by device order by user) rn
    from mtable t
) t
group by device
order by device

You can expand the select clause with more conditional expressions as needed. When the users of a given device exhaust, the following columns show null values.