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

1、SQL中合并多行记录的方法总汇- =- Title: 在SQL中分类合并数据行- Author: dobear Mail(MSN): dobear_0922- Environment: Vista + SQL2005- Date: 2008-04-22- =-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,

2、 'bbb'UNION ALL SELECT 2, 'ccc'-SELECT * FROM tb/*id value- -1 aa1 bb2 aaa2 bbb2 ccc(5 row(s) affected)*/-2 在SQL2000只能用自定义函数实现-2.1 创建合并函数fn_strSum，根据id合并value值GOCREATE FUNCTION dbo.fn_strSum(id int)RETURNS varchar(8000)ASBEGINDECLARE values varchar(8000)SET values = ''SELECT

3、values = values + ',' + value FROM tb WHERE id=idRETURN STUFF(values, 1, 1, '')ENDGO- 调用函数SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BYidDROP FUNCTIONdbo.fn_strSum-2.2 创建合并函数fn_strSum2，根据id合并value值GOCREATE FUNCTION dbo.fn_strSum2(id int)RETURNS varchar(8000)ASBEGINDECLARE val

4、ues varchar(8000)SELECT values = isnull(values + ',', '') + value FROM tb WHERE id=idRETURN valuesENDGO- 调用函数SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BYidDROP FUNCTIONdbo.fn_strSum2-3 在SQL2005中的新解法-3.1 使用OUTER APPLYSELECT *FROM (SELECT DISTINCT id FROM tb) A OUTERAPPLY(SEL

5、ECT values= STUFF(REPLACE(REPLACE(SELECT value FROMtb NWHERE id =A.idFORXML AUTO), '<N value="', ','), '"/>', ''), 1, 1, '')N-3.2 使用XMLSELECT id, values=STUFF(SELECT ','+value FROM tb t WHERE id=tb.id FOR XML PATH(''), 1, 1, '')FROMtbGROUP BYid-4 删除测试表tb drop