Turning a Comma Separated string into individual rows

csv, split, sql-server, t-sql

Solution

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

Problem

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).

Original source