In SQL Server, I have a query pulling values like:
SELECT ID, NAME, TITLE
FROM TABLE_X
To get results like:
ID NAME TITLE
----------------
01 Bob Cat
02 Bob Bat
03 Tom Mat
I want to run a query to get the following results:
NAME COUNT TITLE
-----------------------
Bob 2 Cat, Bat
Tom 1 Mat
All I have is something like this:
SELECT NAME, COUNT(ID), STUFF(TITLE)
FROM TABLE_X
The STUFF syntax will be as shown in this below code to get your desired result-
SELECT B.NAME, COUNT(*) [COUNT],
STUFF((
SELECT ',' + A.TITLE
FROM TABLE_X A
WHERE A.NAME = B.NAME
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') TITLE
FROM TABLE_X B
GROUP BY B.NAME
Output is-
NAME COUNT TITLE
Bob 2 Cat,Bat
Tom 1 Mat
You can group by NAME and use the STRING_AGG aggregate function (starting with SQL Server 2017)
SELECT NAME, COUNT(*) AS [COUNT], STRING_AGG(TABLE_X.TITLE, ', ') AS TITLE
FROM TABLE_X
GROUP BY NAME
ORDER BY NAME
Note that only the columns listed in the GROUP BY clause can appear as-is in the SELECT list. You must apply an aggregate function to all the others. I escaped the column alias COUNT, as this is a reserved word.
