Converting a single comma separated row into multiple rows

Creating navigation panel for reports
May 18, 2010
Static Named Sets v/s Dynamic Named Sets
May 24, 2010
Show all

Converting a single comma separated row into multiple rows

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?


  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,
    FROM (SELECT a.[State],
    split.a.VALUE('.', 'VARCHAR(100)') AS string
    FROM (SELECT [State],
    CAST ('' + REPLACE([City], ',', '') + '' AS
    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:


  7. jeeva says:


  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
    ,CAST ('' + REPLACE([Col1], ',', '') + '' AS XML)
    ,CAST ('' + REPLACE([Col2], ',', '') + '' AS XML)

    Declare @ID int
    Declare @MaxID int

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

    WHILE (@ID <= @MaxID)


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

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

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

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


    SET @ID = @ID +1




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

    —-Main Output

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


  16. Mamatha .s. says:

    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)
    declare @g varchar(max)
    SET @g = LEN(@booking) – LEN(LEFT(@booking, CHARINDEX(',',@booking+',')-1))
    set @booking=@booking+','

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

  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:


    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

  24. Ankur says:

    Hi Jason, thanks for your post but following query is not working in SQL developer, moreover if I want to do on two columns what will be the query, please suggest… thanks

    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); – See more at:

  25. Dave says:


    The code works great for columns that contain a comma or other character you want to delimit by, but what about those values which are not delimited? How can a query encompass those as well in the same result.

  26. Om says:

    Any help for PL/SQL code please ?

  27. Daniel Marinho says:

    This was hugely helpful. Congrats!

  28. Vivek says:

    Split.a.value(‘.’, ‘VARCHAR(20)’) AS string

    For the above statement i am getting invalid datatype for XML. Please help me with this.

    Thanks in advance.


  29. Leena says:


    I have following data in Excel. I want to convert semicolon separated cell into rows.

    Id Name Status Contacts

    1234 Test Tesing ACTV Apple Rose; Test Testing; Mtest Testy; Aaaaa Test; Deavc Test; Stest Ctest
    1232 Test Easy ACTV Aasd Ntestl; Mtestt Btesttler; Htest Mtest; Testravis Kesting; Etest Nest


    Id Name Status Contacts

    1234 Test Tesing ACTV Apple Rose
    1234 Test Tesing ACTV Test Testing
    1234 Test Tesing ACTV Mtest Testy
    1234 Test Tesing ACTV Aaaaa Test
    1234 Test Tesing ACTV Deavc Test
    1234 Test Tesing ACTV Stest Ctest
    1232 Test Easy ACTV Aasd Ntestl
    1232 Test Easy ACTV Mtestt Btesttler
    1232 Test Easy ACTV Htest Mtest
    1232 Test Easy ACTV Testravis Kesting
    1232 Test Easy ACTV Etest Nest


  30. M.R. says:

    Great post. thank you!

  31. vasilis says:

    You are the best…. MAN

  32. Derek Broughton says:


    Who cares about the efficiency!

Leave a Reply

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