Pages

Thursday, May 20, 2010

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?

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

    ReplyDelete
  2. Excellent post. Thanks a lot ! Simplest solution on the internet :)

    ReplyDelete
  3. i cannot use group by clause by using this code

    ReplyDelete
  4. 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

    ReplyDelete
    Replies
    1. Above query not working ,you are using group by clause

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

    ReplyDelete
  6. 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

    ReplyDelete
  7. 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???

    ReplyDelete
    Replies
    1. Can you see if this post answers your question - http://fendy-huang.blogspot.com/2012/09/create-ssrs-report-using-dax.html If not, can you send a detailed question (with some sample data) to jason143@gmail.com of what you want to achieve?

      Delete
    2. Thanks for sharing the Url but Pattern Matching is missing in that

      for example if my input is Joh,SMI then my
      required output is to display the details of john and smith

      Pattern matching is achieved by FIND() function in DAX and is not in PATHCONTAINS() function

      need help..

      Delete
    3. Can you send the detailed requirements to my email address listed above? (if you have an excel workbook with some data in it, I would be able to reply to you faster)

      Delete
  8. Very Nice :) Its working perfectly .. Thanks :)

    ReplyDelete
  9. Thank you very very very much Man! Perfect!

    ReplyDelete
  10. 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

    ReplyDelete