SQL merge multiple-line records method Pool _mssql

Summary of methods for merging multiple-line records in SQL:
--1. Create a table, add test data
CREATE TABLE TB (id int, [value] varchar (10))
INSERT TB SELECT 1, ' AA '
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 can only be implemented using custom functions in SQL2000
----2.1 Create a merge function fn_strsum, combining value values by 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
--Calling a function
SELECT ID, VALUE = dbo.fn_strsum (ID) from TB GROUP by ID
DROP FUNCTION Dbo.fn_strsum
----2.2 Create a merge function fn_strsum2, combining value values by 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
--Calling a function
SELECT ID, VALUE = dbo.fn_strsum2 (ID) from TB GROUP by ID
DROP FUNCTION dbo.fn_strsum2
A new method of--3 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 uses 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 Test Table TB
DROP table TB
/**//*
ID values
----------- --------------------
1 AA,BB
2 AAA,BBB,CCC
(2 row (s) affected)
*/