0

This is SQL source Table. I need to concatenate this table like the table below,appreciate your help.

PE_CASE   PE_ITEM   PE_HIS_SEQ     PE_COMMENT

200       0001      9989           Comment 1
200       0001      9990           Comment 2
200       0001      NULL           Comment 3
200       0002      9986           Comment 4           
200       0002      9987           Comment 5
200       0002      NULL           Comment 6
201       0001      9926           Comment 7
201       0001      9937           Comment 8 
201       0001      NULL           Comment 9
201       0002      9933           Comment 10
201       0002      9921           Comment 11
201       0002      NULL           Comment 12

I need to concatenate above table like this.

PE_CASE   PE_ITEM   PE_HIS_SEQ     PE_COMMENT

200       0001      NULL           Comment1 + Comment2 + Comment3 + Comment4
200       0002      NULL           Comment 4  + Comment 5  + Comment 6
201       0001      NULL           Comment 7  + Comment 8  + Comment 9
201       0002      NULL           Comment 10 + Comment 11 + Comment 12
2

You can use for xml to concatenate values from multiple rows. There are many other ways to concatenate rows as outlined here

For XML example with your data above, first we create and load sample table

    CREATE TABLE [pecomment]([PE_CASE]    int,
                    [PE_ITEM]    varchar(4),
                    [PE_HIS_SEQ] int,
                    [PE_COMMENT] varchar(20));

INSERT INTO [pecomment]
VALUES
      (200, 0001, 9989, 'Comment 1'),
      (200, 0001, 9990, 'Comment 2'),
      (200, 0001, NULL, 'Comment 3'),
      (200, 0002, 9986, 'Comment 4'),
      (200, 0002, 9987, 'Comment 5'),
      (200, 0002, NULL, 'Comment 6'),
      (201, 0001, 9926, 'Comment 7'),
      (201, 0001, 9937, 'Comment 8'),
      (201, 0001, NULL, 'Comment 9'),
      (201, 0002, 9933, 'Comment 10'),
      (201, 0002, 9921, 'Comment 11'),
      (201, 0002, NULL, 'Comment 12');

Then use for xml to concatenate the comments, grouping by PE_CASE and PE_ITEM

SELECT [PE_CASE],
      [PE_ITEM],
      NULL,
      [PE_COMMENT] = SUBSTRING((
      SELECT '+ '+[PE_COMMENT]
      FROM [pecomment] AS [t2]
      WHERE [t1].[PE_CASE] = [t2].[PE_CASE]
           AND [t1].[PE_ITEM] = [t2].[PE_ITEM]
      ORDER BY [PE_CASE],
             [PE_ITEM]
      FOR XML PATH('')
      ), 3, 1000)
FROM [pecomment] AS [t1]
GROUP BY [PE_CASE],
        [PE_ITEM];

This should give you what I think is your example desired result, hence the null in PE_HIS_SEQ.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.