SQL中合并多行記錄的方法總匯

1、SQL中合并多行記錄的方法總匯- =- Title: 在SQL中分類合并數(shù)據(jù)行- Author: dobear Mail(MSN): dobear_0922- Environment: Vista + SQL2005- Date: 2008-04-22- =-1. 創(chuàng)建表，添加測試數(shù)據(jù)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只能用自定義函數(shù)實現(xiàn)-2.1 創(chuàng)建合并函數(shù)fn_strSum，根據(jù)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- 調(diào)用函數(shù)SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BYidDROP FUNCTIONdbo.fn_strSum-2.2 創(chuàng)建合并函數(shù)fn_strSum2，根據(jù)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- 調(diào)用函數(shù)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