如何在SQLite中转置表格？

Question

您好，所以我在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

我想对表进行转置，因此每行只有一个 用户。 列标题为 “ Group” 。“组”的值将是第一张表的 “角色” 列中的值。

因此，转换后的外观如下：

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

一个人如何去做这个SQLite？

Answer 1

您可以使用row_number()＆进行聚合：

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;