CODEWITHSUNDEEP
sqlsql-serveraggregate-functionsstring-aggstuff
ND_OG_89
ND_OG_89

Reputation: 13

Group By with Multiple Stuff Statements - SQL

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

Upvotes: -2

Views: 77

Answers (2)

Charlieface
Charlieface

Reputation: 72395

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);

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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

Upvotes: 0

Related Questions