How to transpose a table in SQLite?

Question

Hello so I have a table as such in SQlite:

   User    |  Group  |   Role    
John Smith |   A     |   admin
John Smith |   B     |   user
Jane Doe   |   A     |   user
Jane Doe   |   B     |   limit
Jane Doe   |   C     |   admin
Jack Brown |   A     |   admin, user

I want to transpose the table so there is only one user per row.The column headers are "Group". The values for "Group" would be values in the "Role" column from the first table.

So, it would look as such when transformed:

   User    |    A        |    B       |  C 
John Smith |   admin     |    user    |
Jane Doe   |   user      |    limit   | admin
Jack Brown |   admin,user|            |

How would one go about doing this SQLite?

Heng

Looks to me like a pivot. Then this is a duplicate of: https://mcmap.net/q/338272/-how-to-pivot-in-sqlite-or-i-e-select-in-wide-format-a-table-stored-in-long-format – At

Answer 1

You can use row_number() & do aggregation :

select User, 
       max(case when seq = 1 then role end) as a,
       max(case when seq = 2 then role end) as b,
       max(case when seq = 3 then role end) as c
from (select t.*,
             row_number() over (partition by User order by group) as seq
      from table t
     ) t
group by User;

Dunstable

This assumes that the group values will be contiguous starting from A. If there is a missing group between other group values, the [seq] numbering would put them in the incorrect column. – Pegboard

Answer 2

The excellent solution offered by @CPerkins has the potential drawback of losing information. For example, consider what would happen if the data for "Jack Brown" was presented in two rows:

Jack Brown |   A     |   admin
Jack Brown |   A     |   user

To ensure no information is lost, one could use GROUP_CONCAT instead of MAX:

SELECT User, 
       GROUP_CONCAT(CASE WHEN "group" == 'A' THEN role END) as A,
       GROUP_CONCAT(CASE WHEN "group" == 'B' THEN role END) as B,
       GROUP_CONCAT(CASE WHEN "group" == 'C' THEN role END) as C
FROM SO52961250 t
GROUP BY User;

Airlie

Answer 3

No need to use windowing functions since the "Group" column already provides the necessary value on which to transform. Not only does this simplify the query, but it also puts the values in the correct transformed column irregardless of the order or whether or not the "group" values are contiguous. (Also note that sqlite complains if group is used without delimiters since it is reserved keyword.)

SELECT User, 
       max(CASE WHEN "group" == 'A' THEN role END) as A,
       max(CASE WHEN "group" == 'B' THEN role END) as B,
       max(CASE WHEN "group" == 'C' THEN role END) as C
FROM SO52961250 t
GROUP BY User;

Pegboard