sqlsql-serverxmlxqueryfor-xml-path

Using GROUP BY with FOR XML PATH in SQL Server 2016

I am trying to

  1. group by ID and
  2. 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 comments for all IDs. I am struggling where and how to add "group by" clause in my query.

Here is the query:

create table Comments (ID int, Comment nvarchar(150), RegionCode int)


insert into Comments values (1006, 'I', 1)
, (1006, 'am', 1)
, (1006, 'good', 1)
, (1006, 'bad', 2)
, (2, 'You', 1)
, (2, 'are', 1)
, (2, 'awesome', 1)


SELECT 
    SUBSTRING((SELECT Comment
               FROM Comments
               WHERE ID = 1006 AND RegionCode != 2
               FOR XML PATH('')), 1, 999999) AS Comment_Agg

My desired result looks something like this:

image

FYI, I am using FOR XML PATH here to aggregate multiple comments into a single row because STRING_AGG function is not supported in my version - SQL Server 2016 (v13.x).

Solution

  • Please try the following solution.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (ID int, Comment nvarchar(150));
INSERT INTO @tbl VALUES 
(1006, 'I'),
(1006, 'am'),
(1006, 'good'),
(2, 'You'),
(2, 'are'),
(2, 'awesome');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT p.ID
   , STUFF((SELECT @separator + Comment 
          FROM @tbl AS c
          WHERE c.ID = p.ID
          FOR XML PATH('')), 1, LEN(@separator), '') AS Result
FROM @tbl AS p
GROUP BY p.ID
ORDER BY p.ID;

    Output

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