Turning a Comma Separated string into individual rows

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,    DataItemFROM tmpORDER 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        

Finally, the wait is over with SQL Server 2016. They have introduced the Split string function, STRING_SPLIT:

select OtherID, cs.Value --SplitDatafrom yourtablecross apply STRING_SPLIT (Data, ',') cs

All the other methods to split string like XML, Tally table, while loop, etc.. have been blown away by this STRING_SPLIT function.

Here is an excellent article with performance comparison: Performance Surprises and Assumptions: STRING_SPLIT.

For older versions, using tally table here is one split string function(best possible approach)

CREATE FUNCTION [dbo].[DelimitedSplit8K]        (@pString VARCHAR(8000), @pDelimiter CHAR(1))RETURNS TABLE WITH SCHEMABINDING AS RETURN--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...     -- enough to cover NVARCHAR(4000)  WITH E1(N) AS (                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL                  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1                ),                          --10E+1 or 10 rows       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front                     -- for both a performance gain and prevention of accidental "overruns"                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4                ),cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)                 SELECT 1 UNION ALL                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter                ),cteLen(N1,L1) AS(--==== Return start and length (for use in substring)                 SELECT s.N1,                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)                   FROM cteStart s                )--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),        Item       = SUBSTRING(@pString, l.N1, l.L1)   FROM cteLen l;

Referred from Tally OH! An Improved SQL 8K “CSV Splitter” Function

Check this

 SELECT A.OtherID,       Split.a.value('.', 'VARCHAR(100)') AS Data   FROM   (     SELECT OtherID,           CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data       FROM  Table1 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);