Post reply

select from multiple comma separated string

  • May 13, 2012 at 3:29 am

    #72451

    declare @str1 as VARCHAR(100)='2,5,4,3,7,7'

    declare @str2 as VARCHAR(100)='5,6,7,8,9,0'

    declare @str3 as VARCHAR(100)='2,5,4,3,7,7'

    I want output from select like

    2 5 2

    5 6 5

    4 7 4

    3 8 3

    7 9 7

    7 0 7

    How can I do this ?

  • May 13, 2012 at 10:23 am

    #1486871

    Like this...

    SELECT f1.ItemNumber,

    Str1 = f1.Item,

    Str2 = f2.Item,

    Str3 = f3.Item

    FROM dbo.DelimitedSplit8K(@str1,',') f1

    INNER JOIN dbo.DelimitedSplit8K(@str2,',') f2

    ON f1.ItemNumber = f2.ItemNumber

    INNER JOIN dbo.DelimitedSplit8K(@str3,',') f3

    ON f1.ItemNumber = f3.ItemNumber

    ;

    Get the "DelimitedSplit8K" function from the "resources" section near the end of the following article.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Let us know if you have any questions on it all.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

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