Converting a single comma separated row into multiple rows

1-Home-Page-section_thumb1
Creating navigation panel for reports
May 18, 2010
result-1_thumb1
Static Named Sets v/s Dynamic Named Sets
May 24, 2010
Show all

Converting a single comma separated row into multiple rows

Convert-comma-seperated-row-to-multiple-rows_thumb1

Ever since I wrote Converting multiple rows into a single comma separated row, I was trying to find a SQL command which will do the reverse, which is converting the single comma separated row back to multiple rows. I checked up a few blogs and I found out that it was possible to do with the help of custom functions or stored procedures, but I was not interested in all of them. Finally I got the answer that I was looking for, and now that I did, I did not waste precious time in noting it down here, lest I forget. Let me try to show you what we are trying to achieve here with the help of an image

Convert comma seperated row to multiple rows

This can be done with the help of the below query

 SELECT A.[State],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [State],  
         CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Again, no stored procedures or function, just plain old SQL 🙂

Update (23/05/2010) : I saw this blog by Brad Schulz (MVP) explaining this scenario and the performance implications in detail. So people who are serious about getting this in their code, please read his blog before you do so – Delimited String Tennis Anyone?

31 Comments

  1. Tried and tested. Very nice, I was using a stored procedure and string parsing to do this. I will have to look into the XML support in SQL this is very nice indeed.

  2. Ragu says:

    Excellent post. Thanks a lot ! Simplest solution on the internet 🙂

  3. Anonymous says:

    i cannot use group by clause by using this code

  4. SQL_Jason says:

    Could you explain your exact scenario? I am able to use a group by clause as shown in the query below
    SELECT state,
    COUNT(*)
    FROM (SELECT a.[State],
    split.a.VALUE('.', 'VARCHAR(100)') AS string
    FROM (SELECT [State],
    CAST ('' + REPLACE([City], ',', '') + '' AS
    XML)
    AS string
    FROM tablea) AS a
    CROSS APPLY string.NODES ('/M') AS split(a)) a
    GROUP BY state

  5. Anonymous says:

    Thanks for replying.
    Is that possible to use group by clause for City.

  6. Anonymous says:

    thanks

  7. jeeva says:

    thanks

  8. anil ch says:

    Hi Guys,

    Just wanted to extend a little for the above code.

    Let's say below is the Data in a given table

    ID Col1 Col2 Col3 Col4
    1 111,112,113 2010-01-01,2010-01-15,2010-01-30 Books Pencils
    2 114,115 2011-01-01,2011-01-15,2011-01-30 Chicago Altlanta
    3 116,117 2012-01-01,2012-01-15,2012-01-30 Sky Moon

    and the OUTPUT should like this below. with 1-1 correspondence for the separated commas in the data.

    ID Col1 Col2 Col3 Col4
    1 111 2010-01-01 00:00:00.000 Books Pencils
    1 112 2010-01-15 00:00:00.000 Books Pencils
    1 113 2010-01-30 00:00:00.000 Books Pencils
    2 114 2011-01-01 00:00:00.000 Chicago Altlanta
    2 115 2011-01-15 00:00:00.000 Chicago Altlanta
    3 116 2012-01-01 00:00:00.000 Sky Moon
    3 117 2012-01-15 00:00:00.000 Sky Moon

    the below code can be used.

    CREATE TABLE #Table (ID int IDENTITY(1,1),Col1 nvarchar(100),Col2 nvarchar(100),Col3 nvarchar(100),Col4 nvarchar(100))
    CREATE TABLE #Table2 (ID int ,Col1 XML,Col2 XML,Col3 nvarchar(100),Col4 nvarchar(100))
    CREATE TABLE #Table3 (ID int,ImpID int IDENTITY(1,1), Col1 nvarchar(100),Col3 nvarchar(100),Col4 nvarchar(100))
    CREATE TABLE #Table4 (ID int,ImpID int IDENTITY(1,1), Col2 datetime,Col3 nvarchar(100),Col4 nvarchar(100))
    CREATE TABLE #Table5 (ID int,ImpID int,Col1 nvarchar(100),Col3 nvarchar(100),Col4 nvarchar(100))
    CREATE TABLE #Table6 (ID int,ImpID int,Col2 datetime,Col3 nvarchar(100),Col4 nvarchar(100))

    INSERT INTO #Table VALUES('111,112,113','2010-01-01,2010-01-15,2010-01-30','Books','Pencils')
    INSERT INTO #Table VALUES('114,115','2011-01-01,2011-01-15,2011-01-30','Chicago','Altlanta')
    INSERT INTO #Table VALUES('116,117','2012-01-01,2012-01-15,2012-01-30','Sky','Moon')

    INSERT INTO #Table2
    SELECT
    ID
    ,CAST ('' + REPLACE([Col1], ',', '') + '' AS XML)
    ,CAST ('' + REPLACE([Col2], ',', '') + '' AS XML)
    ,Col3
    ,Col4
    FROM
    #Table

    Declare @ID int
    Declare @MaxID int

    SET @ID = (SELECT MIN(ID) FROM #Table)
    SET @MaxID = (SELECT MAX(ID) FROM #Table)

    WHILE (@ID <= @MaxID)

    BEGIN

    INSERT INTO #Table3(ID,Col1,Col3,Col4)
    SELECT pvs.ID as ID
    ,Col1 = p.p.value('.', 'nvarchar(100)')
    ,Col3
    ,Col4
    FROM
    #Table2 pvs
    CROSS APPLY pvs.Col1.nodes('//*') p(p)
    WHERE
    pvs.ID = @ID

    INSERT INTO #Table4(ID,Col2,Col3,Col4)
    SELECT pvs.ID as ID
    ,Col1 = p.p.value('.', 'datetime')
    ,Col3
    ,Col4
    FROM
    #Table2 pvs
    CROSS APPLY pvs.Col2.nodes('//*') p(p)
    WHERE
    pvs.ID = @ID

    INSERT INTO #Table5
    SELECT ID,ImpID,Col1,Col3,Col4 FROM #Table3
    WHERE
    ID = @ID

    INSERT INTO #Table6
    SELECT ID,ImpID,Col2,Col3,Col4 FROM #Table4
    WHERE
    ID = @ID

    TRUNCATE TABLE #Table3
    TRUNCATE TABLE #Table4

    SET @ID = @ID +1

    END

    –END

    –INSERT INTO #

    SELECT * FROM #Table
    –SELECT * FROM #Table2
    –SELECT * FROM #Table5
    –SELECT * FROM #Table6

    —-Main Output

    SELECT
    a.ID,a.Col1,b.Col2,a.Col3,b.Col4
    FROM
    #Table5 a
    JOIN #Table6 b ON a.ID = b.ID
    AND a.ImpID = b.ImpID

    DROP TABLE #Table
    DROP TABLE #Table2
    DROP TABLE #Table3
    DROP TABLE #Table4
    DROP TABLE #Table5
    DROP TABLE #Table6

  9. HI Jason,

    I need exact logic in DAX. Scenario as follows..

    I have a parameter (Text data type) in which User can give either student name or student number, Accordingly I have added a filter in Calculatetable as below

    FILTER(Student,find(lower(@StudentNameNumber), lower(Student[StudentName]), 1, -1) > -1 || find(lower(@StudentNameNumber), lower(Student[StudentNumber]), 1, -1) > -1)

    Now, my requirement is

    StudentNameNumber Parameter should now accept multiple values separated by comma…

    for example john,smith or 23,24

    there is no function in Dax as such to separate a string into sub strings.

    any ideas???

  10. Thanks a lot sir Hurrey. That was i really need it.

  11. Aziz Sukla says:

    Thank you man. You are great!

  12. Thanks, great solution 🙂

  13. Sona says:

    Very Nice 🙂 Its working perfectly .. Thanks 🙂

  14. Thank you very very very much Man! Perfect!

  15. Hi Jason,

    Thanks for your post.

    I have the same requirement at my end and was trying the SQL you have shared but I am getting few errors:

    1. Split.a.value (cannot find either column "split" or user defined function or aggregate "split.a.value".)

    2. String.nodes (invalid object name "String.nodes")

    Please suggest what can be the issue.

    Database: SQL Server 2008.

    Regards,
    Tauceef

  16. Mamatha .s. says:

    Hi,
    The Error same as Oracle Apex Solutions is appearing to me also.. Can u plz tell how can i resolve it..

  17. declare @booking varchar(50)='15474,15480,1500,12342,456456,123444,3465478709'
    declare @NoOfChar decimal(18,0)
    declare @book as table (id decimal(18,0))
    declare @value varchar(50)
    set @NoOfChar = LEN(@booking)
    while(@NoOfChar !=0)
    begin
    declare @g varchar(max)
    SET @g = LEN(@booking) – LEN(LEFT(@booking, CHARINDEX(',',@booking+',')-1))
    if(@g=0)
    begin
    set @booking=@booking+','

    set @value=substring(@booking,1,PATINDEX('%,%',@booking)-1)
    insert into @book values(@value)
    select * from @book
    end
    else
    begin
    set @value=substring(@booking,1,PATINDEX('%,%',@booking)-1)
    insert into @book values(@value)
    end
    set @booking= STUFF(@booking,1,PATINDEX('%,%',@booking),'')
    set @NoOfChar=@g
    end

  18. BishNaboB says:

    This is excellent, exactly what I needed. Thank you!

  19. arif says:

    Thank you, you solved lot of my issue in this matter, thanks a lot

  20. Vijay krishnan says:

    Hi,

    I have a comma seperated string @input = ‘Test1, test2, test3’. How to insert this string as a individual record in the table.

  21. Radi says:

    it worked with me as Sql… but not Oracle what can I do… 🙁

    oracle doesn’t accept cross apply

  22. KevinT says:

    The query at the top of the page works great except for when I have a State but no City. I would like to have the City as null for this instance. What can I do? Thank you in advance

  23. Rajeesh says:

    Can We Use the One Column name in Twice in the Above Split Query..? If it is Possible Please Share the query. its More Important to me. Already i Have Used but i getting the Error as Column name ‘XYZ’ is already Used… Thanx in Advance

Leave a Reply

Your email address will not be published. Required fields are marked *