Post reply

Split comma separate single row into multiple rows

  • December 28, 2011 at 8:06 am

    #391215

    Hi guys,

    I have a table variable object and records are present in that table as follow:

    DECLARE @Table AS TABLE(tbls VARCHAR(MAX),i_Status TINYINT)

    INSERT @Table SELECT '17311',2

    UNION ALL

    SELECT '17314,17315',2

    UNION ALL

    SELECT '17329',3

    SELECT * FROM @Table

    Now, I need to separate 17314,17315 ie. commas. So mine result should be like attached file:

    Any help on this would be greatly appreciated.

    Thanks in advance!

  • December 28, 2011 at 8:36 am

    #1426118

    Jeff Moden has an article on this very subject. String Splitter[/url]

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 28, 2011 at 8:43 am

    #1426123

    Edited bad post... Sorry. 🙂

  • December 28, 2011 at 8:45 am

    #1426124

    Edited bad post... Sorry. 🙂

  • December 28, 2011 at 7:13 pm

    #1426299

    Drew is right! you can follow Jeff Moden[/url]'s article[/url] about the tally table. It is a very good article and helps me a lot. You can check that out if you want to learn more. 🙂

    BTW this code must solve your problem.

    --::Create a tally table---------------------------

    select top 1000 identity(int, 1,1)[N]

    into #tempTally

    from master..syscolumns a, master..syscolumns b

    --::Your code---------------------------------------

    DECLARE @Table AS TABLE(tbls VARCHAR(MAX),i_Status TINYINT)

    INSERT @Table SELECT '17311',2

    UNION ALL

    SELECT '17314,17315',2

    UNION ALL

    SELECT '17329',3

    --::Do the trick here-------------------------------

    select substring(',' + tbls + ',', N + 1, charindex(',', ',' + tbls + ',', N + 1) - N - 1)[tbls],

    i_Status

    from #tempTally a

    cross apply @Table b

    where a.N = charindex(',', ',' + tbls + ',', N) and

    a.N < len(',' + tbls + ',')

    ----------------------------------------------------

    "Often speak with code not with word,
    A simple solution for a simple question"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply