String Merge after group by in SQL Server

Reference:

1. After the SQL query statement group BY, the string merge

2. SQL FOR XML Path usage

#需求:

 Merge the column value table structure with the following data: ID value-----  ------ 1AA1BB2AAA2BBB2CCC needs to get results: IDValues -----  ------------ 1AA,BB2AAA,BBB,CCC is:  Group  by ID, sum of Value (string addition) 

#解决方法: 1, traditional method: Create a handler function (SQL Server 2000 can only use this method)

Create TableTB (IDint, valuevarchar(Ten)) Insert  intoTbValues(1,'AA') Insert  intoTbValues(1,'BB') Insert  intoTbValues(2,'AAA') Insert  intoTbValues(2,'BBB') Insert  intoTbValues(2,'CCC') Go --1. Create a handler functionCREATE FUNCTIONDbo.f_strunite (@id int) RETURNS varchar(8000)  as BEGIN     DECLARE @str varchar(8000)     SET @str = "'     SELECT @str = @str + ',' +Value fromTbWHEREId=@id     RETURN STUFF(@str,1,1,"') END GO --calling FunctionsSELECtID, value=Dbo.f_strunite (ID) fromTbGROUP  byIDDrop TableTBDrop functionDbo.f_struniteGo/*ID value---------------------1 aa,bb 2 AAA,BBB,CCC (the number of rows affected is 2 rows)*/ 

2. New workaround: Take advantage of the FOR XML path (SQL Server 2005 and later versions)

SelectId[Values]=Stuff(    (        Select ','+[value]  fromTB TwhereId=Tb.id forXML Path ("')    ),    1,     1,     "')  fromTBGroup  byID/*ID values-----------------------------1 aa,bb 2 AAA,BBB,CCC (2 row (s) affected)*/ 

String Merge after group by in SQL Server