SQL Row Data to Column Data in Join

Trying to wrap my tiny brain around how to write this query and I am at a loss:

Current:

How I would like it to look:

If someone can point me to a function that could help me with this, it would be much appreciated. I already tried using:

max(case when ....) and pivot()

which I found in other articles, but could not get them to work with my JOIN:

SELECT GROUP_ID, ROLE_ID, USER_ID, First_Name, Last_name, Phone
FROM GroupRoles
FULL JOIN Account
ON Account.Account_ID = GroupRoles.User_ID

Any help would be MUCH appreciated to at least get me pointed in the right direction…

Answer

This should do it. If you need the number of roles to be flexible it would be more complicated. You would need to create your sql in a string and use exec sp_executesql to execute it

select * from
(
    select GROUP_ID, 'ROLE_' + convert(varchar, ROLE_ID) + '_' + Keys as Keys, Vals from
    (
        SELECT GROUP_ID, ROLE_ID, USER_ID, convert(varchar, First_Name) as First_Name, convert(varchar, Last_name) as Last_name, convert(varchar, Phone) as Phone
        FROM GroupRoles
        FULL JOIN Account
        ON Account.Account_ID = GroupRoles.User_ID
    ) t
    UNPIVOT  
    (
        Vals FOR Keys IN   
        (First_Name, Last_name, Phone)  
    ) AS unpvt
) AS SourceTable  
PIVOT  
(  
  MAX(Vals)  
  FOR Keys IN (
        ROLE_1_First_Name,
        ROLE_1_Last_name,
        ROLE_1_Phone,
        ROLE_2_First_Name,
        ROLE_2_Last_name,
        ROLE_2_Phone,
        ROLE_3_First_Name,
        ROLE_3_Last_name,
        ROLE_3_Phone,
        ROLE_4_First_Name,
        ROLE_4_Last_name,
        ROLE_4_Phone,
        ROLE_5_First_Name,
        ROLE_5_Last_name,
        ROLE_5_Phone
    )  
) AS PivotTable