SQL 合并多行记录的方法总汇

--1. 创建表，添加测试数据 
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 在SQL2000只能用自定义函数实现 
----2.1 创建合并函数fn_strSum，根据id合并value值 
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 

-- 调用函数 
SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id 
DROP FUNCTION dbo.fn_strSum 

----2.2 创建合并函数fn_strSum2，根据id合并value值 
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 

-- 调用函数 
SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id 
DROP FUNCTION dbo.fn_strSum2 


--3 在SQL2005/SQL2008中的新解法 
----3.1 使用OUTER APPLY 
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, '') 
)N 

----3.2 使用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 删除测试表tb 
drop table tb 

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

(2 row(s) affected) 
*/