SQL statement to merge multiple rows of records

The method for merging multiple rows of records is summarized.

The method for merging multiple rows of records is summarized.

-- 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
),' ', ''), 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)
*/