Home Forums Article Discussions Article Discussions by Author Discuss content posted by Carl P. Anderson Concatenating Rows RE: Concatenating Rows

  • October 14, 2009 at 2:03 am

    #1065525

    nicolay.tchernitsky (10/13/2009)

    the XML solution can also be:

    select replace(

    convert(

    varchar(max),

    (select name+',' as [text()] from fruit order by id for xml path(''))

    )+',,,'

    ,',,,,'

    ,'')

    or, if there is no need in deleting the last comma and converting the result from xml to string, just

    select name+',' as [text()] from fruit order by id for xml path('')

    Flip it around and delete the first comma...

    with

    fruit as (

    select 'Apple' as name, 101 as id UNION ALL

    select 'Banana' as name, 102 as id UNION ALL

    select 'Orange' as name, 103 as id UNION ALL

    select 'Melon' as name, 104 as id UNION ALL

    select 'Grape' as name, 105 as id

    )

    SELECT STUFF((select ','+name from fruit order by id for xml path('')),1,1,'') AS CSV

    --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)