如何在m到n关系表中将行转换为新列? [英] How to convert rows to new columns in m to n relation ship table?

问题描述

我需要一些帮助来解决问题.

I need some help with a problem.

我正在使用SQL在Microsoft数据库上工作，并且有一个与此表类似的表:

I am working on a microsoft database with SQL and I have a Table that looks similar to this one:

|*Name*|*Permissions*|
|App1  |Permission 1 |
|App1  |Permission 2 |
|App1  |Permission 3 |
|App2  |Permission 1 |
|App2  |Permission 2 |
|App2  |Permission 3 |

所以这是一个m对n的关系，因为有多个权限分配给一个应用程序，但也有多个应用程序使用相同的权限.

So it is an m to n relation ship, as there are multiple permissions assigned to an app but there are also multiple apps using the same permission.

我已经尝试过使用诸如以下这样的方法将表与其自身连接起来:

I have already tried something like joining the table with itself using distinct like this:

select distinct apps1.name, apps2.permissions from Apps apps1
join Apps apps2
where apps2.name IN
(SELECT distinct name from Apps);

但是它并没有给我我想要的结果.

but it doesnt get me the result I am looking for.

我想要一个看起来像这样的结果

I would like to get a result that looks like this:

|*Name*|*Permission1*|*Permission2*|*Permission3*|
|App1  |Permission 1 |Permission 2 |Permission 3 |
|App2  |Permission 1 |Permission 2 |Permission 3 |

应用程序可能具有不同数量的权限，但是如果是这样，则可以将这些空列填充为NULL值.

It is possible, that apps have got a different amount of permissions, but if it is so, it would be okay if these empty columns are filled with NULL values.

希望您能帮助我. 如果需要进一步的信息，请给我写信.因为我是StackOverflow的新手，所以我不是写下问题的专业人士.

I hope you can help me. If any further information is needed, just write me. As I am new to StackOverflow I am not a professional in writing down the question.

推荐答案

如果您拥有最大数量的权限(例如三个)，则可以使用条件聚合(或pivot):

If you have a maximum number of permissions that you care about (say three), you can just use conditional aggregation (or pivot):

select a.name,
       max(case when seqnum = 1 then a.permission end) as permission_1,
       max(case when seqnum = 2 then a.permission end) as permission_2,
       max(case when seqnum = 3 then a.permission end) as permission_3
from (select a.*,
             row_number() over (partition by a.name order by a.name) as seqnum
      from apps a
     ) a
group by a.name;

如果您不知道要返回多少列，则需要使用动态SQL，查询会更加复杂.

If you don't know how many columns you want returned, then you need to use dynamic SQL and the query is a bit more complicated.

这篇关于如何在m到n关系表中将行转换为新列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！