Selecting multiple values from a column and put them into separate columns

Question

I have a table like this:

MainUser | PartyUser
--------------------
George   | Tom
Michael  | Tim
Michael  | James
Michael  | Monica
Jim      | Nick
Jim      | Connie

And I need to get something like this:

MainUser | PartyUser1 | PartyUser2 | PartyUser3 | PartyUser4 ...
----------------------------------------------------------------
George   | Tom        |            |            |
Michael  | Tim        | James      | Monica     |
Jim      | Nick       | Connie     |            |

Can someone help to find a way doing this in sql?
I'm having a hard time figuring this out, so any help would be greatly appreciated.

Answer 1

The simple way (assuming you already know all the party users) would be to use pivot() and row_number() statements.

select  MainUser, 
        [1] as PartyUser1, 
        [2] as PartyUser2, 
        [3] as PartyUser3, 
        [4] as PartyUser4, 
        [5] as PartyUser5, 
        [6] as PartyUser6
from (
    select *, row_number() over(partition by MainUser order by PartyUser) as rn
    from party_table
    ) t
PIVOT (max(PartyUser) for rn in ([1], [2], [3], [4], [5], [6])) as u