我试图建立一个SQL视图以返回团队中的球员。到目前为止,我已经得到了这样的结果。
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
感谢您的任何帮助。
最佳答案
尝试这个
为了识别每个人,我使用了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
关于c# - 然后,SQL Group By将值放在新记录的自己的列中,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/36669447/