SQL query Statement group BY, string merge

Original: SQL query statement group BY, string merge

Merge column Values--*******************************************************************************************table structure with the following data: ID value----- ------1AA1BB2AAA2BBB2CCC needs to get results: ID values------ -----------1AA,BB2AAA,BBB,CCC namely: Group by ID, sum of Value (string addition)1The old workaround (can only be resolved with functions in SQL Server 2000.) ) --=============================================================================CREATE TABLE TB (IDint, Value varchar (Ten) ) insert into TB values (1,'AA') insert into TB values (1,'BB') insert into TB values (2,'AAA') insert into TB values (2,'BBB') insert into TB values (2,'CCC') Go--1. Create a handler function Dbo.f_strunite (@idint) RETURNS varchar (8000) as BEGIN DECLARE @str varchar (8000) SET @str="'SELECT @str= @str +','+ value from TB WHERE id=@id RETURN STUFF (@str,1,1,"') END GO--Call function SELECt ID, value=Dbo.f_strunite (ID) from TB GROUP by ID drop table TB drop function Dbo.f_strunite Go/*ID value----------------------1 aa,bb 2 AAA,BBB,CCC (the number of rows affected is 2 rows)*/--===================================================================================2new workaround (in SQL Server 2005 with outer apply, etc.). ) CREATE TABLE TB (IDint, Value varchar (Ten) ) insert into TB values (1,'AA') insert into TB values (1,'BB') insert into TB values (2,'AAA') insert into TB values (2,'BBB') insert into TB values (2,'CCC') Go--Query Processing SELECT*From (select DISTINCT ID from TB) A OUTER APPLY (SELECT [Values]=STUFF (replace (replace ( (SELECT value from TB N WHERE ID=a.id for XML AUTO),'<n value= "',','),'"/>',"'),1,1,"') ) N drop table TB/*ID values----------------------1 aa,bb 2 AAA,BBB,CCC (2 rows affected)*/--Method 2 in SQL2005 CREATE TABLE TB (IDint, Value varchar (Ten) ) insert into TB values (1,'AA') insert into TB values (1,'BB') insert into TB values (2,'AAA') insert into TB values (2,'BBB') insert into TB values (2,'CCC') GoSelectID, [Values]=stuff (Select ','+[value] fromTB TwhereId=tb.id forXML Path ("')),1,1,"')  fromTB GROUP BY ID/*ID values-------------------------------1 aa,bb 2 AAA,BBB,CCC (2 row (s) affected)*/ 

Demo

SelectBegincity,endcity,fandian,[Cangwei]=Stuff((Select '/'+[Cangwei]  fromtest1 TwhereT.begincity=Test1.begincity andT.endcity=Test1.endcity andT.fandian=Test1.fandian forXML Path ("')),1,1,"')  fromTest1Group  byBegincity,endcity,fandian

Reference: http://bbs.csdn.net/topics/330188225

http://bbs.csdn.net/topics/330182340

SQL query Statement group BY, string merge