title: Splitting SQL column into multiple columns based on value
body: <p>I have a table like the below</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <code>NULL</code> values so the table should like the below</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My code is currently:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>Unfortunately this returns these results:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I assume to get this working you need to join the code back on itself but I can't seem to get it working.</p>