如何在 SQLite 中转置表? [英] How to transpose a table in SQLite?

问题描述

你好，我在 SQlite 中有一个这样的表格:

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|            |

如何去做这个 SQLite?

How would one go about doing this SQLite?

推荐答案

您可以使用 row_number() &做聚合:

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;

这篇关于如何在 SQLite 中转置表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！