title: Turning a Comma Separated string into individual rows
body: <p>I have a SQL Table like this:</p>
  
  <pre><code>| SomeID         | OtherID     | Data
  +----------------+-------------+-------------------
  | abcdef-.....   | cdef123-... | 18,20,22
  | abcdef-.....   | 4554a24-... | 17,19
  | 987654-.....   | 12324a2-... | 13,19,20
  </code></pre>
  
  <p>is there a query where I can perform a query like <code>SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......'</code> that returns individual rows, like this:</p>
  
  <pre><code>| OtherID     | SplitData
  +-------------+-------------------
  | cdef123-... | 18
  | cdef123-... | 20
  | cdef123-... | 22
  | 4554a24-... | 17
  | 4554a24-... | 19
  </code></pre>
  
  <p>Basically split my data at the comma into individual rows?</p>
  
  <p>I am aware that storing a <code>comma-separated</code> string into a relational database sounds dumb, but the normal use case in the consumer application makes that really helpful.</p>
  
  <p>I don't want to do the split in the application as I need paging, so I wanted to explore options before refactoring the whole app.</p>
  
  <p>It's <code>SQL Server 2008</code> (non-R2).</p>