CHAR column concenation

  • Hey gang,

    I'm a little stumped here. I want to concatenate character data from a table.

    declare @ttest table

    (nItemintegernot null,

    ctextvarchar(32)not null)

    insert @ttest values (1, 'Row 1 text')

    insert @ttest values (1, 'Row 2 text')

    insert @ttest values (1, 'Row 3 text')

    insert @ttest values (2, 'Row 1 text')

    insert @ttest values (2, 'Row 2 text')

    insert @ttest values (2, 'Row 3 text')

    insert @ttest values (2, 'Row 4 text')

    insert @ttest values (2, 'Row 5 text')

    I want to get a 2 column result set that looks like

    1, 'Row 1 text Row 2 text Row 3 text'

    2, 'Row 1 text Row 2 text Row 3 text Row 4 text Row 5 text'

    Is this possible w/ out a temp table? I'm desperately trying to avoid the dreaded nested Insert/Exec error.

    Thanks!

  • there's a neat trick with FOR XML you can use:

    declare @ttest table

    (nItemintegernot null,

    ctextvarchar(32)not null)

    insert @ttest values (1, 'Row 1 text')

    insert @ttest values (1, 'Row 2 text')

    insert @ttest values (1, 'Row 3 text')

    insert @ttest values (2, 'Row 1 text')

    insert @ttest values (2, 'Row 2 text')

    insert @ttest values (2, 'Row 3 text')

    insert @ttest values (2, 'Row 4 text')

    insert @ttest values (2, 'Row 5 text')

    SELECT nItem,stuff(( SELECT ',' + ctext

    FROM @ttest s2

    WHERE s2.nItem= s1.nItem --- must match GROUP BY below

    ORDER BY ctext

    FOR XML PATH('')

    ),1,1,'') as [Itemss]

    FROM @ttest s1

    GROUP BY s1.nItem --- without GROUP BY multiple rows are returned

    ORDER BY s1.nItem

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you Lowell, that's brilliant.

    I *really* must find some time to bone up on XML.

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

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