2


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.

2
  • Please see this question - seems to be a very similar problem. Feb 1, 2017 at 16:12
  • Thank you for your response. I took a look over the question but the answer from irimias was closer to what I wanted to do.
    – Rudy
    Feb 2, 2017 at 13:44

1 Answer 1

3

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
2
  • Thank you! It is almost what I needed. I think I have to make some adjustments because sometimes it skips over some party users and sometimes it puts null values between party users.
    – Rudy
    Feb 2, 2017 at 8:33
  • It's ok now, I got it working. Thank you again!
    – Rudy
    Feb 2, 2017 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.