Turning a Comma Separated string into individual rows

Question

I have a SQL Table like this:

| SomeID         | OtherID     | Data +----------------+-------------+------------------- | abcdef-.....   | cdef123-... | 18,20,22 | abcdef-.....   | 4554a24-... | 17,19 | 987654-.....   | 12324a2-... | 13,19,20 

is there a query where I can perform a query like SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......' that returns individual rows, like this:

| OtherID     | SplitData +-------------+------------------- | cdef123-... | 18 | cdef123-... | 20 | cdef123-... | 22 | 4554a24-... | 17 | 4554a24-... | 19 

Basically split my data at the comma into individual rows?

I am aware that storing a comma-separated string into a relational database sounds dumb, but the normal use case in the consumer application makes that really helpful.

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.

It's SQL Server 2008 (non-R2).

Answer 1

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata (     SomeID INT,     OtherID INT,     String VARCHAR(MAX) );  INSERT Testdata SELECT 1,  9, '18,20,22'; INSERT Testdata SELECT 2,  8, '17,19'; INSERT Testdata SELECT 3,  7, '13,19,20'; INSERT Testdata SELECT 4,  6, ''; INSERT Testdata SELECT 9, 11, '1,2,3,4'; 

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS (     SELECT         SomeID,         OtherID,         LEFT(String, CHARINDEX(',', String + ',') - 1),         STUFF(String, 1, CHARINDEX(',', String + ','), '')     FROM Testdata     UNION all      SELECT         SomeID,         OtherID,         LEFT(String, CHARINDEX(',', String + ',') - 1),         STUFF(String, 1, CHARINDEX(',', String + ','), '')     FROM tmp     WHERE         String > '' ) SELECT     SomeID,     OtherID,     DataItem FROM tmp ORDER BY SomeID; -- OPTION (maxrecursion 0) -- normally recursion is limited to 100. If you know you have very long -- strings, uncomment the option 

---

Output

 SomeID | OtherID | DataItem  --------+---------+----------  1      | 9       | 18         1      | 9       | 20         1      | 9       | 22         2      | 8       | 17         2      | 8       | 19         3      | 7       | 13         3      | 7       | 19         3      | 7       | 20         4      | 6       |            9      | 11      | 1          9      | 11      | 2          9      | 11      | 3          9      | 11      | 4