Concat based on sequence number

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.

Answer

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;