資源描述：
《sql中合并多行記錄的方法總匯》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、SQL中合并多行記錄的方法總匯--?=============================================================================--?Title:?在SQL中分類合并數(shù)據(jù)行--?Author:?dobear????????Mail(MSN):?dobear_0922@hotmail.com--?Environment:?Vista?+?SQL2005--?Date:?2008-04-22--?==============================================
2、===============================--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,?'bbb'UNION?ALL?SELECT?2,?'ccc'--SELECT?*?FROM?tb/**//*id??????????value-----------?---------
3、-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)ASBEGIN????DECLARE?@values?varchar(8000)????SET?@values?=?''?
4、???SELECT?@values?=?@values?+?','?+?value?FROM?tb?WHERE?id=@id????RETURN?STUFF(@values,?1,?1,?'')ENDGO--?調(diào)用函數(shù)SELECT?id,?VALUE?=?dbo.fn_strSum(id)?FROM?tb?GROUP?BY?idDROP?FUNCTION?dbo.fn_strSum----2.2?創(chuàng)建合并函數(shù)fn_strSum2，根據(jù)id合并value值GOCREATE?FUNCTION?dbo.fn_strSum2(@id?int)RETUR
5、NS?varchar(8000)ASBEGIN????DECLARE?@values?varchar(8000)????????SELECT?@values?=?isnull(@values?+?',',?'')?+?value?FROM?tb?WHERE?id=@id????RETURN?@valuesENDGO--?調(diào)用函數(shù)SELECT?id,?VALUE?=?dbo.fn_strSum2(id)?FROM?tb?GROUP?BY?idDROP?FUNCTION?dbo.fn_strSum2--3?在SQL2005中的新解法----3.1?
6、使用OUTER?APPLYSELECT?*?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????????????),?'',?''),?1
7、,?1,?''))N----3.2?使用XMLSELECT?id,?[values]=STUFF((SELECT?','+[value]?FROM?tb?t?WHERE?id=tb.id?FOR?XML?PATH('')),?1,?1,?'')FROM?tbGROUP?BY?id--4?刪除測試表tbdrop?table?tb/**//*id??????????values-----------?--------------------1???????????aa,bb2???????????aaa,bbb,ccc(2?row(s)?affec
8、ted)*/--來自csdn,這篇文章很不錯.
