title: SQL Server comma-separated column to multiple columns
body: <p>I currently have a table of three columns; <code>Org</code>, <code>Group ID</code> and <code>Role</code>.</p>
  
  <p><img src="https://i.stack.imgur.com/uClgW.jpg" alt="enter image description here"></p>
  
  <p>The column called <code>Org</code> can be ignored. </p>
  
  <p>What I have been trying to do is to group them based on <code>Group ID</code> and split each of the <code>Roles</code> that belongs to the same <code>Group ID</code> to a single column.</p>
  
  <p>So far I could manage to group the records and put the <code>Roles</code> of the same group in the same column.</p>
  
  <p><img src="https://i.stack.imgur.com/DncyY.jpg" alt="enter image description here"></p>
  
  <p>I used the following script:</p>
  
  <pre><code>Select 
      [Group ID],  
      Substring((Select ',' +  [Role] 
                 From [dbo].[ASA_test] B 
                 Where B.[Group ID] = A.[Group ID]] 
                 For XML Path('')), 2, 8000) As List  
  From 
      [dbo].[ASA_test]  A
  Group By 
      [Group ID] 
  </code></pre>
  
  <p>Later on I created a temp table variable and inserted the SQL above in it to use this variable in a user defined function afterwards to have the outcome. </p>
  
  <p>Please see below:</p>
  
  <pre><code>DECLARE @MyTempTableVariable TABLE
  (
      GroupID int,
      Role nvarchar(255)
  )
  
  INSERT INTO @MyTempTableVariable
     Select 
         [Group ID],  
         Substring((Select ',' +  [Role] 
                    From [dbo].[ASA_test] B 
                    Where B.[Group ID] = A.[Group ID]] 
                    For XML Path('')), 2, 8000) As List  
     From 
         [dbo].[ASA_test]  A
     Group By 
         [Group ID] 
  </code></pre>
  
  <p>However I couldn't go any further as it didn't work as I expected.</p>
  
  <p>Can someone please help? please note that the total number of records are not limited with what I post here. </p>
  
  <p>Thanks!</p>