How to aggregate repeating fields into seperate colums

Question

following situation, pos/neg_text are repeating:

This is my feedback table:

id  text-id    pos_text         neg_text       cat_id
1   1          nice people      null           21 
2   1          nice people      null           24 
3   1          nice people      null           27 
4   1          null             cold room      36
5   1          null             cold room      37

I would like to bring all these data into a single row like:

id  text-id    pos_text         neg_text       pos_cat_id       neg_cat_id
1   1          nice people      cold room      "21,24,27"       "36,37"   

How should my SQL query look like? I am implementing an algorithm with TypeScript in my middleware to solve this problem, and maybe there are DB-specialists can solve that much more elegant with a simple SQL-Query.

Using SQL Server 2008

Answer 1

Due to the use of such an old version of SQL Server, this is not easy. You lack simple tools like CONCAT, for implicit conversion, and more recent functions like STRING_AGG, meaning you need to use 3 scans of the table to achieve what could be done in 1 scan.

Instead we need to use the "Old " FOR XML PATH method here to aggregate the values, and the use CONVERT rather than a simple CONCAT(',',sq.catid). I've used 3 as the length, but you likely need something longer.

WITH YourTable AS(
    SELECT *
    FROM (VALUES(1,1,'nice people',null,21), 
                (2,1,'nice people',null,24), 
                (3,1,'nice people',null,27), 
                (4,1,null,'cold room',36),
                (5,1,null,'cold room',37))V(id,textid,postext,negtext,catid))
SELECT MIN(ID) AS ID,
       textid,
       MAX(postext) AS postext,
       MAX(negtext) AS negtext,
       STUFF((SELECT ',' + CONVERT(varchar(3),sq.catid)
              FROM YourTable sq
              WHERE sq.textid = YT.textid
                AND sq.postext IS NOT NULL
              ORDER BY sq.id
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'') AS poscatid,
       STUFF((SELECT ',' + CONVERT(varchar(3),sq.catid)
              FROM YourTable sq
              WHERE sq.textid = YT.textid
                AND sq.negtext IS NOT NULL
              ORDER BY sq.id
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'') AS negtextid
FROM YourTable YT
GROUP BY YT.textid;

db<>fiddle

As a note, this is how simple it is on more recent versions of SQL:

SELECT MIN(ID) AS ID,
       textid,
       MAX(postext) AS postext,
       MAX(negtext) AS negtext,
       STRING_AGG(CASE WHEN postext IS NOT NULL THEN catid END,',') WITHIN GROUP (ORDER BY ID) AS poscatid,
       STRING_AGG(CASE WHEN negtext IS NOT NULL THEN catid END,',') WITHIN GROUP (ORDER BY ID) AS negcatid
FROM YourTable
GROUP BY textid;