小编典典

如何在SQLite中转置表格?

sql

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


阅读 43

收藏
2021-04-07

共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;
2021-04-07