類比ORACLE wm_concat函數（把一列中的多行資料在一行顯示）

 
from：http://topic.csdn.net/u/20081225/16/0b224585-228b-4b2c-80db-b574b252d9cc.html
SQL code問題描述：
無論是在sql 2000，還是在 sql 2005 中,都沒有提供字串的彙總函式，  所以，當我們在處理下列要求時，會比較麻煩：有表tb, 如下：id    value----- ------1     aa1     bb2     aaa2     bbb2     ccc需要得到結果：id     values------ -----------1      aa,bb2      aaa,bbb,ccc即， group by id, 求 value 的和（字串相加）1. 舊的解決方案-- 1. 建立處理函數CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN    DECLARE @r varchar(8000)    SET @r = ''    SELECT @r = @r + ',' + value    FROM tb    WHERE id=@id    RETURN STUFF(@r, 1, 1, '')ENDGO-- 調用函數SELECt id, values=dbo.f_str(id) FROM tb GROUP BY id-- 2. 新的解決方案 -- 樣本資料DECLARE @t TABLE(id int, value varchar(10))INSERT @t 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(    SELECT DISTINCT         id    FROM @t)AOUTER APPLY(    SELECT         [values]= STUFF(REPLACE(REPLACE(            (                SELECT value FROM @t N                WHERE id = A.id                FOR XML AUTO            ), '<N value="', ','), '"/>', ''), 1, 1, ''))N/*--結果id          values----------- ----------------1           aa,bb2           aaa,bbb,ccc(2 行受影響)--*/--各種字串分函數--3.3.1 使用遊標法進行字串合并處理的樣本。--處理的資料CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并處理--定義結果集表變數DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定義遊標並進行合并處理DECLARE tb CURSOR LOCALFORSELECT col1,col2 FROM tb ORDER BY  col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SELECT @col1_old=@col1,@s=''WHILE @@FETCH_STATUS=0BEGIN    IF @col1=@col1_old        SELECT @s=@s+','+CAST(@col2 as varchar)    ELSE    BEGIN        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))        SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1    END    FETCH tb INTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--顯示結果並刪除測試資料SELECT * FROM @tDROP TABLE tb/*--結果col1       col2---------- -----------a          1,2b          1,2,3--*/GO/*==============================================*/--3.3.2 使用使用者定義函數，配合SELECT處理完成字串合并處理的樣本--處理的資料CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO--合并處理函數CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN    DECLARE @re varchar(100)    SET @re=''    SELECT @re=@re+','+CAST(col2 as varchar)    FROM tb    WHERE col1=@col1    RETURN(STUFF(@re,1,1,''))ENDGO--調用函數SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1--刪除測試DROP TABLE tbDROP FUNCTION f_str/*--結果col1       col2---------- -----------a          1,2b          1,2,3--*/GO/*==============================================*/--3.3.3 使用暫存資料表實現字串合并處理的樣本--處理的資料CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并處理SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tbORDER BY col1,col2DECLARE @col1 varchar(10),@col2 varchar(100)UPDATE #t SET     @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,    @col1=col1,    col2=@col2SELECT * FROM #t/*--更新處理後的暫存資料表col1       col2---------- -------------a          1a          1,2b          1b          1,2b          1,2,3--*/--得到最終結果SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1/*--結果col1       col2---------- -----------a          1,2b          1,2,3--*/--刪除測試DROP TABLE tb,#tGO/*==============================================*/--3.3.4.1 每組 <=2 條記錄的合并--處理的資料CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'c',3--合并處理SELECT col1,    col2=CAST(MIN(col2) as varchar)        +CASE             WHEN COUNT(*)=1 THEN ''            ELSE ','+CAST(MAX(col2) as varchar)        ENDFROM tbGROUP BY col1DROP TABLE tb/*--結果col1       col2      ---------- ----------a          1,2b          1,2c          3--*/--3.3.4.2 每組 <=3 條記錄的合并--處理的資料CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3UNION ALL SELECT 'c',3--合并處理SELECT col1,    col2=CAST(MIN(col2) as varchar)        +CASE             WHEN COUNT(*)=3 THEN ','                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)            ELSE ''        END        +CASE             WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)            ELSE ''        ENDFROM tb aGROUP BY col1DROP TABLE tb/*--結果col1       col2---------- ------------a          1,2b          1,2,3c          3--*/GOif not object_id('A') is null    drop table AGoCreate table A([id] int,[cname] nvarchar(2))Insert Aselect 1,N'張三' union allselect 2,N'李四' union allselect 3,N'王五' union allselect 4,N'蔡六'Go--> -->  if not object_id('B') is null    drop table BGoCreate table B([id] int,[cname] nvarchar(5))Insert Bselect 1,N'1,2,3' union allselect 2,N'3,4'Gocreate function F_str(@cname nvarchar(100))returns nvarchar(100)asbegin select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0return @cnameendgoselect [id],dbo.F_str([cname])[cname] from Bid          cname----------- ----------------------------------------------------------------------------------------------------1           張三,李四,王五2           王五,蔡六(2 個資料列受到影響)