Concat based on sequence number

Question

I'm trying to concat a text field based on a separate field and sequence number. For this example let's say the fields I have are employee ID, Sequence#, and Comments. The comments field is limited by a certain number of characters so there can be multiple comment fields for the same employee ID sorted by the sequence number. I would like to get 1 line per employee ID and concat all the comments together into one.

Current Data

Expected Data

Post the tables and data as text [READ THIS](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Juan Carlos Oropeza, Aug 20 '19 at 15:47
https://database.guide/the-sql-server-equivalent-to-group_concat/ SQL SERVER 2017 has `STRING_AGG()` — Juan Carlos Oropeza, Aug 20 '19 at 15:51
Probably the [`STUFF()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-2017) function. — Joel Coehoorn, Aug 20 '19 at 15:52

score 0 · Answer 1 · answered Aug 20 '19 at 16:28

Assuming SQL Server 2017 or higher, use string_agg:

SELECT Employee_Id, 
       1 As Sequence, 
       STRING_AGG(Comment, CHAR(10)+CHAR(13)) WITHIN GROUP (ORDER BY Sequence)
FROM TableName
GROUP BY Employee_Id

For lower versions, use a combination of for xml and stuff, like in this post.

score 0 · Accepted Answer · answered Aug 20 '19 at 17:09

What @zohar posted is how I would do it with SQL 2017+. On Pre-2017 systems you'll have to go the XML PATH route.

DECLARE @table TABLE (employee_ID INT, [sequence] INT, Comment VARCHAR(100));
INSERT @table VALUES (1234,1,'Ate Pizza'),(1234,2,'..then more bread'),
                     (9999,1,'Drank Malort'),(9999,2,'... then regretted it');

SELECT 
  t.Employee_Id, 
  [Sequence] = 1, 
  Comment    = 
  (
    SELECT Comment+'' 
    FROM   @table AS t2
    WHERE  t.employee_ID = t2.employee_ID 
    FOR XML PATH('')
  )  --Comment = STRING_AGG(Comment, CHAR(10)+CHAR(13)) WITHIN GROUP (ORDER BY [Sequence])
FROM     @Table AS t
GROUP BY Employee_Id;

Returns:

Employee_Id Sequence    Comment
----------- ----------- -----------------------------------------
1234        1           Ate Pizza..then more bread
9999        1           Drank Malort... then regretted it

To protect against special XML characters (a problem STRING_AGG does not have) you would append the code to look like this:

SELECT 
  t.Employee_Id, 
  [Sequence] = 1, 
  Comment    = 
  (
    SELECT Comment+'' 
    FROM   @table AS t2
    WHERE  t.employee_ID = t2.employee_ID 
    FOR XML PATH(''), TYPE
  ).value('text()[1]','varchar(100)') 
FROM     @Table AS t
GROUP BY Employee_Id;

Great thanks, I am pre-2017 so the xml path route worked. – jlive Aug 21 '19 at 15:10 — jlive, Aug 21 '19 at 15:10

Concat based on sequence number

2 Answers2