How to get values separated by comma into new record in SQL Server?

Question

Could some one please explain how to get values separated by comma into a new record in SQL Server based on below mentioned scenario.

I have a table like this

Column 1  |  Column 2
----------+--------------
abc       |  12345
bcd       |  13455,45678
sdf       |  78934,13345

I want the result to be in the following way

Column1 |  Column2
--------+----------
abc     |   12345
bcd     |   13455
bcd     |   45678
sdf     |   78934
sdf     |   13345

**Don't store data like this in the first place!** Hopefully the point of this query is to fix the bad schema design. — Joel Coehoorn, Sep 01 '17 at 21:50
I don't know SQL Server, but a quick search suggests that [this should get you started](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows). Something like `SELECT t.Column1, cs.value AS Column2 FROM YourTable t CROSS APPLY STRING_SPLIT(t.Column2) cs` — GolezTrol, Sep 01 '17 at 21:51
This has been asked and answered many times. https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Jon Ekiz, Sep 01 '17 at 22:01
@JonEkiz - The answer that's marked is a crap answer. Recursive CTEs aren't an efficient way to parse strings... at least not compared to tally tables or tally functions. — Jason A. Long, Sep 01 '17 at 22:26

Jason A. Long · Answer 1 · 2017-09-01T22:22:29.517

Start with a good tally based string splitting function like Jeff Moden's DelimitedSplit8K

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  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
;
GO

Now your problem becomes a very simple matter...

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;

CREATE TABLE #temp (
    Column1 CHAR(3),
    Column2 VARCHAR(8000)
    );  
INSERT #temp (Column1,Column2) VALUES
    ('abc', '12345'),
    ('bcd', '13455,45678'),
    ('sdf', '78934,13345');

-- the actual query...
SELECT 
    t.Column1,
    dsk.Item
FROM 
    #temp t
    CROSS APPLY dbo.DelimitedSplit8K(t.Column2, ',') dsk;

Results...

Column1 Column2
------- --------
abc     12345
bcd     13455
bcd     45678
sdf     78934
sdf     13345

EDIT: The above makes the assumption that Column2 can have any number of elements in the CSV string. If the maximum number of elements is two, you can skip the splitter function and use something like the following...

SELECT 
    t.Column1,
    v.Column2
FROM 
    #temp t
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX(',', t.Column2, 1), 0)) ) s (Split)
    CROSS APPLY ( VALUES (1, LEFT(t.Column2, s.Split - 1)), (2, substring(t.Column2, ISNULL(s.Split, 0) + 1, 8000)) ) v (rn, Column2)
WHERE
    v.Column2 IS NOT NULL
ORDER BY
    v.rn;

John Cappelletti · Accepted Answer · 2017-09-01T22:50:32.303

Jason's answer would be my first choice (1+)

However, in case you can't use (or want) a TVF, here is an in-line approach.

Example

Select A.[Column 1] 
      ,[Column 2] = B.RetVal
 From  YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B2.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace([Column 2],',','</x><x>')+'</x>' as xml).query('.')) B1
                Cross Apply x.nodes('x') AS B2(i)
             ) B

Returns

Column 1    Column 2
abc         12345
bcd         13455
bcd         45678
sdf         78934
sdf         13345

score 0 · Answer 3 · answered Sep 01 '17 at 22:46

You can use a Tally table based splitter like below:

select 
    column1,
    split_values
from 
(
    select 
        t.column1,
        SUBSTRING( t.column2, t1.N, ISNULL(NULLIF(CHARINDEX(',',t.column2,t1.N),0)-t1.N,8000)) as split_values
    from @t t 
        join
        (
            select 
                t.column2,
                1 as N 
            from @t t  
                UNION ALL
            select 
                t.column2,
                t1.N + 1 as N
            from @t t 
                join
                (
                 select 
                    top 8000
                        row_number() over(order by (select NULL)) as N 
                 from 
                    sys.objects s1 
                        cross join 
                   sys.objects s2 
                ) t1 
            on SUBSTRING(t.column2,t1.N,1) = ','
         ) t1
         on t1.column2=t.column2
)a
order by column1

See live demo

How to get values separated by comma into new record in SQL Server?

3 Answers3