SQL statement to merge multiple rows of records

Method for merging multiple rows of records in SQL:
-- 1. Create a table and add Test Data
Create table tb (id int, [value] varchar (10 ))
INSERT tb SELECT 1, 'A'
Union all select 1, 'bb'
Union all select 2, 'aaa'
Union all select 2, 'bbb'
Union all select 2, 'ccc'
-- SELECT * FROM tb
/**//*
Id value
---------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 row (s) affected)
*/
-- 2 in SQL2000, only user-defined functions can be used.
---- 2.1 create the merging function fn_strSum and merge value values based on id
GO
Create function dbo. fn_strSum (@ id int)
RETURNS varchar (8000)
AS
BEGIN
DECLARE @ values varchar (8000)
SET @ values =''
SELECT @ values = @ values + ',' + value FROM tb WHERE id = @ id
Return stuff (@ values, 1, 1 ,'')
END
GO
-- Call a function
SELECT id, VALUE = dbo. fn_strSum (id) FROM tb group by id
Drop function dbo. fn_strSum
---- 2.2 create the merging function fn_strSum2 and merge value values based on id
GO
Create function dbo. fn_strSum2 (@ id int)
RETURNS varchar (8000)
AS
BEGIN
DECLARE @ values varchar (8000)
SELECT @ values = isnull (@ values + ',', '') + value FROM tb WHERE id = @ id
RETURN @ values
END
GO
-- Call a function
SELECT id, VALUE = dbo. fn_strSum2 (id) FROM tb group by id
Drop function dbo. fn_strSum2
-- 3 new solution in SQL2005/SQL2008
---- 3.1 Use OUTER APPLY
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
---- 3.2 use XML
SELECT id, [values] = STUFF (SELECT ',' + [value] FROM tb t WHERE id = tb. id for xml path (''), 1, 1 ,'')
FROM tb
Group by id
-- 4 Delete the test table tb
Drop table tb
/**//*
Id values
-------------------------------
1 aa, bb
2 aaa, bbb, ccc
(2 row (s) affected)
*/