Concatenate of strings

  • Guys,

    I have following table where I need to concatenate varchar column.

    For example

    IDCOMMENT

    __________________

    1JOHN SMITH

    1 SURRENDER

    1TO COPS

    I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1

    Is there any way to accomplish this?

    Any suggestions and inputs would help

    Thanks

  • You have a few choices here: The two best being an UDF and the other being XML.

    You can get more information from the following thread. Additionally, Jeff has posted a function that will do the same thing. In some cases the UDF process the data much faster, but you will have to test in your environment to see what suites your needs best.

    http://www.sqlservercentral.com/Forums/Topic465637-149-1.aspx

    The XML solution is below:

    SELECT id,

    STUFF((SELECT ' ' + comment

    FROM @t a

    WHERE a.ID = b.ID

    FOR XML PATH(''))

    ,1,1,'')AS String

    FROM @t b

    GROUP BY id

  • There is another way to acheive this result using COALESCE.

    CREATE TABLE test

    (id int

    , comment varchar(255))

    INSERT INTO test

    VALUES (1, 'JOHN SMITH')

    INSERT INTO test

    VALUES (1, 'SURRENDER')

    INSERT INTO test

    VALUES (1, 'TO COPS')

    SELECT * FROM test

    DECLARE @colList varchar(1000)

    SELECT @colList = COALESCE(@colList + ' ', '') + comment

    FROM test

    WHERE id = 1

    SELECT @colList

    DROP TABLE test

    Note: If there are too many rows to concatenate, change the @collist variable length to varchar(max)

  • Thanks for the reply, when I try to group by multiple columns I get an error using XML path is there any way round it

    SELECT id, todate

    STUFF((SELECT ' ' + comment

    FROM TEST2 a

    WHERE a.ID = b.ID and a.todate = b.todate

    FOR XML PATH(''))

    ,1,1,'')AS String

    FROM TEST2 b

    GROUP BY id, todate

    I get the following error message

    "Msg 156, Level 15, State 1, Line 5

    Incorrect syntax near the keyword 'FOR'.

    "

    Is there any way round it

    Thanks

  • You are missing a comma (,) after todate. Here is the corrected code:

    SELECT id, todate,

    STUFF((SELECT ' ' + comment

    FROM TEST2 a

    WHERE a.ID = b.ID and a.todate = b.todate

    FOR XML PATH(''))

    ,1,1,'')AS String

    FROM TEST2 b

    GROUP BY id, todate

    😎

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

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