title: Sql server Pivot and create multiple records from one field and split other field
body: <p>What my data looks like:</p>
  
  <pre><code>Name:   Animals(One-Column):
  Bill    Dog, Cat, Horse
  Bob     Dog, Giraffe, Snake , Horse
  </code></pre>
  
  <p>What I need to transform it to:</p>
  
  <pre><code>Name:   Animal:
  Bill    Dog
  Bill    Cat
  Bill    Horse
  Bob     Dog
  Bob     Giraffe
  Bob     Snake
  Bob     Horse
  </code></pre>
  
  <p>I am unsure of the steps to take, there are anywhere from 0 - 100 values in the actual csv field. </p>
  
  <p><strong>You will notice that the csv's are not consistent</strong> (Horse is word 3 for Bill but word 4 for Bob), so I need a solution like:</p>
  
  <pre><code>1st Name  1st Word
  1st Name  2nd Word
  2nd Name  1st Word
  2nd Name  2nd Word
  2nd Name  3rd Word
  ...
  </code></pre>