How to use GROUP BY to concatenate strings in SQL Server?

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)SELECT   [ID],  STUFF((    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))     FROM #YourTable     WHERE (ID = Results.ID)     FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')  ,1,2,'') AS NameValuesFROM #YourTable ResultsGROUP BY IDDROP TABLE #YourTable

If it is SQL Server 2017 or SQL Server Vnext, SQL Azure you can use string_agg as below:

select id, string_agg(concat(name, ':', [value]), ', ')from #YourTable group by id

using XML path will not perfectly concatenate as you might expect... it will replace "&" with "&" and will also mess with <" and ">...maybe a few other things, not sure...but you can try this

I came across a workaround for this... you need to replace:

FOR XML PATH(''))

with:

FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')

...or NVARCHAR(MAX) if thats what youre using.

why the hell doesn't SQL have a concatenate aggregate function? this is a PITA.