title: Query for comma-separated ids to comma-separated values
body: <p>I have 2 tables</p>
  
  <p><strong>Departments</strong></p>
  
  <pre><code>ID  Dept
  ---------
  1   HR
  2   Accts
  3   IT
  </code></pre>
  
  <p><strong>Employee</strong></p>
  
  <pre><code>ID Name     Depts
  -------------------
  1  Kevin     2,1
  2  Michelle  1
  3  Troy      1,3
  4  Rheesa    2,3,1
  </code></pre>
  
  <p>I am looking for an output like the following with a SQL query.</p>
  
  <p><strong>Employee depts</strong> </p>
  
  <pre><code>ID Name      Depts
  -------------------------
   1 Kevin     Accts,HR
   2 Michelle  HR
   3 Troy      HR,IT
   4 Rheesa    Accts,IT,HR
  </code></pre>
  
  <p>I have tried the following that join s with depts but results in one row for each dept only. How do i get the above results using a query?</p>
  
  <pre><code>select 
      name, depts, dept 
  from 
      employee
  CROSS APPLY  
      dbo.split_list(employee.depts ,',') split
  inner join 
      dbo.department on  depts= split.value
  order by 
      name
  </code></pre>