c# - SQL Group By然后将值放在新记录中自己的列中

Question

我正在尝试制作一个返回团队中球员的 SQL View 。我已经得到了这样的返回结果。

Team      | Person
--------------------
Red Team  | Jack
Red Team  | Jill
Red Team  | Harry
Blue Team  | Bob
Blue Team  | Benny
Blue Team  | Brian

我如何按团队分组并选择该团队中的每个人并将他们放入自己的列中？所以期望的结果应该是这样的。

Team       | Person | Person | Person |
---------------------------------------
Red Team   | Jack     Jill     Harry
Blue Team  | Bob      Benny    Brian

提前感谢您的帮助。

Answer 1

试试这个

为了识别每个人，我使用了 row_number() 函数，然后在 case 语句中使用它们。

declare @tab table
(
    team varchar(50),
    Person varchar(50)
)

Insert into @tab
    values ('Red Team', 'Jack'),
            ('Red Team', 'Jill'),
            ('Red Team', 'Harry'),
            ('Blue Team', 'Bob'),
            ('Blue Team', 'Banny'),
            ('Blue Team', 'Brian')

SELECT 
    Team,
    MAX(case when PersonKey = 1 then Person end) Person,
    MAX(case when PersonKey = 2 then Person end) Person,
    MAX(case when PersonKey = 3 then Person end) Person
From 
(
        Select Team, 
         Person, 
         ROW_NUMBER() Over (Partition By team Order By Person) as PersonKey 
        From @tab
 ) t 
Group By Team

结果

Team        Person  Person  Person
----------------------------------
Blue Team   Banny   Bob     Brian
Red Team    Harry   Jack    Jill