SQL classic code-merge column values

Merge column values
Original: zhujian
Adapted from: AI xinjue Luo. Lu Hua (flowers bloom in ice and snow forests in the past 18 years)-12-16 Guangdong and Shenzhen

Table structure. The data is as follows:
Id value
-----------
1 aa
1 bb
2 aaa
2 bbb
2 ccc

Expected results:
Id values
-----------------
1 aa, bb
2 aaa, bbb, ccc
That is: group by id, sum of values (string addition)

1. The old solution (in SQL server 2000, only functions can be used .)
-- 1. Create a processing function
Create table tb (id int, value varchar (10 ))
Insert into tb values (1, 'A ')
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

Create function dbo. f_str (@ id int)
RETURNS varchar (8000)
AS
BEGIN
DECLARE @ r varchar (8000)
SET @ r =''
SELECT @ r = @ r + ',' + value FROM tb WHERE id = @ id
Return stuff (@ r, 1, 1 ,'')
END
GO

-- Call a function
SELECt id, value = dbo. f_str (id) FROM tb group by id

Drop table tb
Drop function dbo. f_str

/*
Id value
----------------------
1 aa, bb
2 aaa, bbb, ccc
(The number of affected rows is 2)
*/

-- 2. Another function.
Create table tb (id int, value varchar (10 ))
Insert into tb values (1, 'A ')
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

-- Create a merged Function
Create function f_hb (@ id int)
Returns varchar (8000)
As
Begin
Declare @ str varchar (8000)
Set @ str =''
Select @ str = @ str + ',' + cast (value as varchar) from tb where id = @ id
Set @ str = right (@ str, len (@ str)-1)
Return (@ str)
End
Go

-- Call the custom function to obtain the result:
Select distinct id, dbo. f_hb (id) as value from tb

Drop table tb
Drop function dbo. f_hb

/*
Id value
----------------------
1 aa, bb
2 aaa, bbb, ccc
(The number of affected rows is 2)
*/

2. New Solutions (use outer apply in SQL server 2005 .)
Create table tb (id int, value varchar (10 ))
Insert into tb values (1, 'A ')
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 (
(
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 (id int, value varchar (10 ))
Insert into tb values (1, 'A ')
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

Select id, [values] = stuff (select ',' + [value] from tb t where id = tb. id for xml path (''), 1, 1 ,'')
From tb
Group by id

/*
Id values
-------------------------------
1 aa, bb
2 aaa, bbb, ccc

(2 row (s) affected)

*/

Drop table tb