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.