title: TSQL: Split one column with semicolon separated values into multiple rows
body: <p>Trying to migrate from an old database to a new one in which the way data is stored is a bit different.</p>
  
  <p>In one specific case I have a column with semicolon separated values that I would like to separate into multiple rows.</p>
  
  <p>Here is an example:</p>
  
  <pre><code>SELECT 
      p.idperson, 
      p.roleperson
  FROM person p
  </code></pre>
  
  <p>The TSQL above generates the following output</p>
  
  <pre><code>idperson roleperson
  1001    ;214401;
  1002    ;214201;214401;
  1003    ;212101;
  </code></pre>
  
  <p>I would like to convert this to:</p>
  
  <pre><code>idperson roleperson
  1001    214401
  1002    214401
  1002    214201
  1003    212101
  </code></pre>
  
  <p>That is, I want to split the row with multiple values into two rows. Is this possible without creating cursors or loops?</p>