Using Group By With For Xml Path In Sql Server 2016

I am trying to group by ID and aggregate multiple comments into a single row Right now, I can do the no. 2 part for a single ID (ID = 1006), but I would like to aggregate comment

Solution 1:

Please try the following solution.

SQL

-- DDL and sample data population, startDECLARE@tblTABLE (ID int, Comment nvarchar(150));
INSERTINTO@tblVALUES 
(1006, 'I'),
(1006, 'am'),
(1006, 'good'),
(2, 'You'),
(2, 'are'),
(2, 'awesome');
-- DDL and sample data population, endDECLARE@separatorCHAR(1) = SPACE(1);

SELECT p.ID
   , STUFF((SELECT@separator+ Comment 
          FROM@tblAS c
          WHERE c.ID = p.ID
          FOR XML PATH('')), 1, LEN(@separator), '') ASResultFROM@tblAS p
GROUPBY p.ID
ORDERBY p.ID;

Output

+------+-----------------+|  ID  |Result|+------+-----------------+|2| You are awesome ||1006| I am good       |+------+-----------------+