Bad Dub Bad Dub - 7 months ago 9
SQL Question

SQL Group By then put values n their own column in new record

Im trying to make an SQL view that returns players in a team. I have gotten as far as returning results like this.

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


How can I group by the team and select each of the people in that team and put them into their own column? So the desired results would be something like this.

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


Thanks for any help in advance.

Answer

Try this

To identify each person, i have used row_number() function and then used them in case statement.

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 = 'Person1' then Person end) Person,
    max(case when PersonKey = 'Person2' then Person end) Person,
    max(case when PersonKey = 'Person3' then Person end) Person
from 
(
        Select Team, 
         Person, 
         'Person' + cast(row_number() over (Partition by team order by Person) as varchar) as PersonKey 
        from @tab
 ) f 
Group by Team

Result

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