Splitting SQL column into multiple columns based on value

Question

I have a table like the below

Opp_ID     Role_Name     Role_User_Name
---------------------------------------
1          Lead          Person_one
1          Developer     Person_two
1          Developer     Person_three
1          Owner         Person_four
1          Developer     Person_five

I now need to split the Role_Name column to be 3 different columns based on the values. I need to make sure there are no NULL values so the table should like the below

Opp_ID     Lead        Developer     Owner
--------------------------------------------------
1          Person_one  Person_two    Person_four
1          Person_one  Person_three  Person_four
1          Person_one  Person_five   Person_four

My code is currently:

SELECT
    ID,
    CASE WHEN Role_Name = 'Lead' THEN Role_User_Name ELSE NULL END AS Lead,
    CASE WHEN Role_Name = 'Developer' THEN Role_User_Name ELSE NULL END AS Developer,
    CASE WHEN Role_Name = 'Owner' THEN Role_User_Name ELSE NULL END AS Owner
FROM 
    [table1]
WHERE 
    Role_Name IN ('Lead','Developer','Owner')

Unfortunately this returns these results:

Opp_ID     Lead        Developer     Owner
-------------------------------------------
1          Person_one  NULL          NULL
1          NULL        Person_two    NULL
1          NULL        Person_three  NULL
1          NULL        NULL          Person_four
1          NULL        Person_five   NULL

I assume to get this working you need to join the code back on itself but I can't seem to get it working.

Answer 1

To apply each developer and lead across your owners for an Opp_ID, you'll want something like:

SELECT o.opp_id
    , o.Role_User_Name AS Owner
    , l.Role_User_Name AS Lead
    , d.Role_User_Name AS Developer
FROM t1 AS o
LEFT OUTER JOIN t1 l ON o.opp_id = l.opp_id AND l.Role_Name = 'Lead'
LEFT OUTER JOIN t1 d ON o.opp_id = d.opp_id AND d.Role_Name = 'Developer'
WHERE o.Role_Name = 'Owner'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=da4daea062534245bed474f93ffafbb7