Summarize aggregations into friendly text

This is my table in SQL Server :

number     grade    weight
--------------------------
1         A         185
2         A         179
3         C         191
4         C         192
5         C         193
6         A         194
7         A         196
8         A         188
9         A         187
10        A         200
11        B         201
12        B         202
13        B         203
14        B         191
15        B         187

and I want this output in SQL Server (return this Text with Query not function):

Count of A : 7 , Sum of A : 1329 - Count of B : 5 , Sum of B : 984 - Count of C : 3 , Sum of C : 576

Does anyone know how I can do this?

Answer

Modern Versions

If on SQL Server 2017 or better, you can also do it this way (and this prevents you from having to know in advance and hard-code all the possible grades A, B, C etc., which makes it harder to add D, E, and so on later):

;WITH x AS 
(
  SELECT g = grade, y = CONCAT('Count$g', COUNT(*), ' , Sum$g', SUM(weight)) 
  FROM dbo.TableName
  GROUP BY grade
)
SELECT STRING_AGG(REPLACE(y,'$g',' of ' + g + ' : '),' - ')
       WITHIN GROUP (ORDER BY g)
FROM x;

Output

Count of A : 7 , Sum of A : 1329 – Count of B : 5 , Sum of B : 984 – Count of C : 3 , Sum of C : 576

---

2012, 2014, 2016

If on 2012 – 2016, you can’t use STRING_AGG(), but you can use FOR XML PATH to achieve the same thing:

;WITH x AS
(
  SELECT g = grade, y = CONCAT('Count$g', COUNT(*), ' , Sum$g', SUM(weight)) 
  FROM dbo.TableName
  GROUP BY grade
)
SELECT STUFF((
    SELECT ' - ' + REPLACE(y, '$g', ' of ' + g + ' : ') 
      FROM x 
      ORDER BY g 
      FOR XML PATH(''),
     TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 3, '');

---

2005, 2008, 2008 R2

If you’re on earlier (unsupported and ill-advised) versions (2005 – 2008 R2), you’ll just have to make changes to the latter to perform manual string concatenations (and data type handling) instead of using CONCAT().

;WITH x AS
(
  SELECT g = grade, y = 'Count$g'  + CONVERT(varchar(11), COUNT(*))
                      + ' , Sum$g' + CONVERT(varchar(11), SUM(weight)) 
  FROM dbo.TableName
  GROUP BY grade
)
SELECT STUFF((
    SELECT ' - ' + REPLACE(y, '$g', ' of ' + g + ' : ') 
      FROM x 
      ORDER BY g 
      FOR XML PATH(''),
    TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 3, '');

---

Examples

Working dbfiddle using a table variable