Group By with Multiple Stuff Statements - SQL

Question

I am trying to aggregate multiple rows into a single field (for multiple columns), grouped by a common ID (See below)

This is the what I have

ID  CPT  SVCDate    Vendor 
1   A3A  1/14/2023   A      
2   DC6  1/23/2023   B
1   5WS  4/2/2023    A
3   DC6  5/3/2023    C
3   DC6  6/1/2023    C

I am trying to get:

ID   CPT        SVCDate              Vendor
1    A3A,5WS    1/14/2023,4/2/2023   A,A
2    DC6        1/23/2023            B
3    DC6,DC6    5/3/2023,6/1/2023    C,C

My version of SQL doesn't support STRING_AGG()

Thanks a lot :)

SELECT ID,
       CPT,
       SVCDate,
       Vendor,
       STUFF((SELECT ', '+CPT
              FROM Claims CD
              WHERE CD.ID = D1.ID
          FOR XML PATH ('')),1,2, ''),
       STUFF((SELECT ', '+ CAST(SVCDate AS VARCHAR)
         FROM Claims CD1
         WHERE CD1.ID = D1.ID
         FOR XML PATH ('')),1,2,''),
       STUFF((SELECT ', '+Vendor
         FROM Claims CD2
         WHERE CD2.ID = D1.ID
         FOR XML PATH ('')),1,2,'')
        
FROM Claims D1
GROUP BY ID,
       CPT,
       SVCDate,
       Vendor

The above code is not working. I am expecting this result:

ID   CPT        SVCDate              Vendor
1    A3A,5WS    1/14/2023,4/2/2023   A,A
2    DC6        1/23/2023            B
3    DC6,DC6    5/3/2023,6/1/2023    C,C

[Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) & [String\_agg for SQL Server before 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017). Though, personally, I'd suggest you rethink this requirement. — Thom A, Jun 22 '23 at 13:58
This isn't grouping by `STUFF`, this isn't even the important part. This is an old trick to aggregate strings using *XML*. `STUFF` is there only to remove the extra comma. All supported SQL Server versions have STRING_AGG so this trick is not needed — Panagiotis Kanavos, Jun 22 '23 at 14:02

score 0 · Answer 1 · answered Jun 22 '23 at 14:06

0

You just have extra columns in SELECT and GROUP BY that you don't need

SELECT ID,
       STUFF((SELECT ', '+CPT
              FROM Claims CD
              WHERE CD.ID = D1.ID
          FOR XML PATH ('')),1,2, '') AS CPT,
       STUFF((SELECT ', '+ CAST(SVCDate AS VARCHAR)
         FROM Claims CD1
         WHERE CD1.ID = D1.ID
         FOR XML PATH ('')),1,2,'') AS SVCDate ,
       STUFF((SELECT ', '+Vendor
         FROM Claims CD2
         WHERE CD2.ID = D1.ID
         FOR XML PATH ('')),1,2,'') AS Vendor
        
FROM Claims D1
GROUP BY ID

sqlFiddle

answered Jun 22 '23 at 14:06

Nenad Zivkovic

18,221
6
42
55

Thanks a lot. This really helped. Really appreciate you! – ND_OG_89 Jun 22 '23 at 14:11
1

@ND_OG_89, mark this as the answer to your question and upvote rather than just leave a comment. – Dan Guzman Jun 22 '23 at 14:13
1

I've marked the answer,. I can't upvote yet. I just joined – ND_OG_89 Jun 22 '23 at 14:14
The subqueries should probably ensure that they qualifying *all* the columns, not just the ones in the `WHERE`. 'Tis a bad habit to not do so. – Thom A Jun 22 '23 at 14:20
`FOR XML PATH (''), TYPE).value('text()[1]', 'nvarchar(max)'),1,2,'')` would be wise, in case of XML escaping – Charlieface Jun 22 '23 at 15:41

score 0 · Accepted Answer · answered Jun 22 '23 at 16:02

There is a way to do this without querying the table once for each column. You can just build an XML or JSON object of all the rows, and break them back out again using .nodes or OPENJSON.

Then re-aggregate it using FOR XML PATH('').

SELECT
  c.ID,

  STUFF((
      SELECT ', ' + x2.r.value('(CPT/text())[1]', 'varchar(max)')
      FROM x1.xmlData.nodes('r') x2(r)
      FOR XML PATH(''), TYPE
  ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS CPT,

  STUFF((
      SELECT ', ' + x2.r.value('(SVCDate/text())[1]', 'varchar(max)')
      FROM x1.xmlData.nodes('r') x2(r)
      FOR XML PATH(''), TYPE
  ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS SVCDate,

  STUFF((
      SELECT ', ' + x2.r.value('(Vendor/text())[1]', 'varchar(max)')
      FROM x1.xmlData.nodes('r') x2(r)
      FOR XML PATH(''), TYPE
  ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS Vendor

FROM (
    SELECT DISTINCT c.ID
    FROM Claims c
) c1
CROSS APPLY (
    SELECT CPT, SVCDate, Vendor
    FROM Claims CD2
    WHERE CD2.ID = D1.ID
    FOR XML PATH('r')
) x1(xmlData);

Or with JSON

SELECT
  c.ID,

  STUFF((
      SELECT ', ' + x2.CPT
      FROM OPENJSON(x1.json)
        WITH ( CPT varchar(max) ) x2
      FOR XML PATH(''), TYPE
  ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS CPT,

  STUFF((
      SELECT ', ' + x2.SVCDate
      FROM OPENJSON(x1.json)
        WITH ( SVCDate varchar(max) ) x2
      FOR XML PATH(''), TYPE
  ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS SVCDate,

  STUFF((
      SELECT ', ' + x2.Vendor
      FROM OPENJSON(x1.json)
        WITH ( Vendor varchar(max) ) x2
      FOR XML PATH(''), TYPE
  ).value('text()[1]', 'varchar(max)'), 1, 2, '') AS Vendor

FROM (
    SELECT DISTINCT c.ID
    FROM Claims c
) c1
CROSS APPLY (
    SELECT CPT, SVCDate, Vendor
    FROM Claims CD2
    WHERE CD2.ID = D1.ID
    FOR JSON PATH
) x1(json);

Group By with Multiple Stuff Statements - SQL

2 Answers2