Selecting multiple values from a column and put them into separate columns
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