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?
Advertisement
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, '');