title: Split a string and populate a table for all records in table in SQL Server 2008 R2
body: <p>I have a table <code>EmployeeMoves</code>:</p>
  
  <pre><code>| EmployeeID         | CityIDs    
  +------------------------------
  | 24                 | 23,21,22 
  | 25                 | 25,12,14 
  | 29                 | 1,2,5 
  | 31                 | 7 
  | 55                 | 11,34 
  | 60                 | 7,9,21,23,30 
  </code></pre>
  
  <p>I'm trying to figure out how to expand the comma-delimited values from the <code>EmployeeMoves.CityIDs</code> column to populate an <code>EmployeeCities</code> table, which should look like this:</p>
  
  <pre><code>| EmployeeID         | CityID    
  +------------------------------
  | 24                 | 23 
  | 24                 | 21 
  | 24                 | 22 
  | 25                 | 25 
  | 25                 | 12 
  | 25                 | 14 
  | ... and so on
  </code></pre>
  
  <p>I already have a function called <code>SplitADelimitedList</code> that splits a comma-delimited list of integers into a rowset. It takes the delimited list as a parameter. The SQL below will give me a table with split values under the column <code>Value</code>:</p>
  
  <pre><code>select value from dbo.SplitADelimitedList ('23,21,1,4');
  
  | Value   
  +----------- 
  | 23          
  | 21        
  | 1       
  | 4  
  </code></pre>
  
  <p>The question is: <strong>How do I populate <code>EmployeeCities</code> from <code>EmployeeMoves</code> with a single (even if complex) SQL statement</strong> using the comma-delimited list of <code>CityIDs</code> from each row in the <code>EmployeeMoves</code> table, but without any cursors or looping in T-SQL? I could have 100 records in the <code>EmployeeMoves</code> table for 100 different employees.</p>