sql server - Using T-SQL for Grouping, concatenating and counting

Question

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65646543/using-t-sql-for-grouping-concatenating-and-counting

Answer 1

You can use aggregation in the subquery:

SELECT p.ProductID,
       ISNULL(STUFF( (SELECT CONCAT('-', p2.DefectCode, '(', COUNT(*), ')')
                      FROM Product p2
                      WHERE p2.ProductID = p.ProductID
                      GROUP BY p2.DefectCode
                      FOR XML PATH ('')
                     ), 1, 1, ''
                   ), '') AS Defects    
FROM Product p
GROUP BY p.ProductID;

I assume you are using an older version of SQL Server that doesn't support STRING_AGG().