SQL merge column values and split columns values

merge the column value 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.) )--1creating a handler function 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') gocreate FUNCTION dbo.f_str (@idint) RETURNS varchar (8000) asbegin DECLARE @r varchar (8000) SET @r="'SELECT @r= @r +','+ value from TB WHERE id=@id RETURN STUFF (@r,1,1,"') Endgo--Call function Select ID, value=dbo.f_str (ID) from TB GROUP by Iddrop table Tbdrop function Dbo.f_str/*ID value----------------------1 aa,bb2 AAA,BBB,CCC (the number of rows affected is 2 lines)*/--2, another function. 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--creating a merged function Create functions F_HB (@idint) returns varchar (8000) asBEGIN DECLARE @str varchar (8000)  Set@str ="'  Select@str = @str +','+ CAST (value asvarchar fromTbwhereID =@idSet@str = Right (@str, Len (@str)-1)  return(@str) Endgo--call the custom function to get the result:SelectDistinct ID, DBO.F_HB (ID) asValue fromtbdrop table Tbdrop function DBO.F_HB/*ID value----------------------1 aa,bb2 AAA,BBB,CCC (the number of rows affected is 2 lines)*/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,"')) Ndrop table TB/*ID values----------------------1 aa,bb2 AAA,BBB,CCC (2 rows affected)*/--method 2create in SQL2005 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,"') fromTbgroup by ID/*ID values-------------------------------1 aa,bb2 aaa,bbb,ccc (2 row (s) affected)*/The drop table TB split value has a table TB, as follows: ID value----------- -----------1AA,BB2AAA,BBB,CCC to split the value column by ID, the result is as follows: ID value----------- --------1AA1BB2AAA2BBB2CCC1. The old workaround (SQL Server -) SELECT TOP8000id = IDENTITY (int,1,1Into # from syscolumns A, syscolumns b SELECT a.id, SUBSTRING (A.[values], b.id, CHARINDEX (',', A.[values] +',', b.ID)-b.id) From TB A, # bwhere SUBSTRING (','+ a.[values], b.id,1) =','DROP TABLE #2. New workaround (SQL Server2005) CREATE TABLE TB (IDint, Value varchar ( -) ) insert into TB values (1,'AA,BB') insert into TB values (2,'AAA,BBB,CCC') goselect a.id, B.valuefrom (SELECT ID, [value]= CONVERT (XML,'<root><v>'+ REPLACE ([value],',','</v><v>') +'</v></root>') from TB aouter APPLY (SELECT value= N.v.value ('.','varchar (+)') from A.[value].nodes ('/root/v') N (v)) Bdrop TABLE TB/*ID value-----------------------------------------1 aa1 bb2 AAA2 bbb2 CCC (5 rows affected)*/

SQL Merge column values and split columns values