[SQLite] How to transpose a table?

2 points · submitted Oct 2018 by u/arc100

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

3 comments · 100% upvoted · other discussions

comments

pooerh 1 points · Oct 2018 · Snowflake | SQL Server | PostgreSQL | Impala | Spark

Do you know your ABCs? If you do, you can do something like:

select user
     , max(case
             when grp = 'A' then role
             else null
           end) as A
     , max(case
             when grp = 'B' then role
             else null
           end) as B
     , max(case
             when grp = 'C' then role
             else null
           end) as C

  from reddit_9qwrmj
 group by user

Working example. Assumes (user, grp) uniqueness.

permalink

arc100 [OP] 1 points · Oct 2018

Is there a way to do it, if the list of "group" values are unknown ahead of time?

permalink

pooerh 1 points · Oct 2018 · Snowflake | SQL Server | PostgreSQL | Impala | Spark

Sort of. Here's a working example supporting up to 5 different groups. In our case groups 3 and 4 will always be empty because there are only 3 distinct values of grp - A, B & C. Furthermore we can't name these columns A, B, C because we don't know their names. It's impossible to name these columns appropriately, and have the same number of columns as you have distinct values in grp field, without using dynamic SQL.

EDIT: btw, if you're using SQLite version that supports window functions (3.25+) this can be made more efficient but dbfiddle has 3.18, so I had to improvise.

permalink