合并列值

原著：邹建

改编：爱新觉罗.毓华  2007-12-16  广东深圳

表结构，数据如下：

id    value

----- ------

1     aa

1     bb

2     aaa

2     bbb

2     ccc

需要得到结果：

id     values

------ -----------

1      aa,bb

2      aaa,bbb,ccc

即：groupby id, 求 value 的和（字符串相加）

1. 旧的解决方法(在sql server 2000中只能用函数解决。)

--1. 创建处理函数

createtable tb(id int, value varchar(10))

insertinto tb values(1, 'aa')

insertinto tb values(1, 'bb')

insertinto tb values(2, 'aaa')

insertinto tb values(2, 'bbb')

insertinto tb values(2, 'ccc')

go

CREATEFUNCTION dbo.f_str(@idint)

RETURNSvarchar(8000)

AS

BEGIN

    DECLARE@rvarchar(8000)

    SET@r=''

    SELECT@r=@r+','+ value FROM tb WHERE id=@id

    RETURNSTUFF(@r, 1, 1, '')

END

GO

-- 调用函数

SELECt id, value = dbo.f_str(id) FROM tb GROUPBY id

droptable tb

dropfunction dbo.f_str

/*

id          value     

----------- -----------

1           aa,bb

2           aaa,bbb,ccc

（所影响的行数为 2 行）

*/

--2、另外一种函数.

createtable tb(id int, value varchar(10))

insertinto tb values(1, 'aa')

insertinto tb values(1, 'bb')

insertinto tb values(2, 'aaa')

insertinto tb values(2, 'bbb')

insertinto tb values(2, 'ccc')

go

--创建一个合并的函数

createfunction f_hb(@idint)

returnsvarchar(8000)

as

begin

  declare@strvarchar(8000)

  set@str=''

  select@str=@str+','+cast(value asvarchar) from tb where id =@id

  set@str=right(@str , len(@str) -1)

  return(@str)

End

go

--调用自定义函数得到结果：

selectdistinct id ,dbo.f_hb(id) as value from tb

droptable tb

dropfunction dbo.f_hb

/*

id          value     

----------- -----------

1           aa,bb

2           aaa,bbb,ccc

（所影响的行数为 2 行）

*/

2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)

createtable tb(id int, value varchar(10))

insertinto tb values(1, 'aa')

insertinto tb values(1, 'bb')

insertinto tb values(2, 'aaa')

insertinto tb values(2, 'bbb')

insertinto tb values(2, 'ccc')

go

-- 查询处理

 

SELECT * FROM(SELECT DISTINCT id FROM tb) A OUTER APPLY(        SELECT[value]=STUFF(REPLACE(REPLACE(            (                SELECT value FROM tb N                WHERE N.id = A.id                FOR XML AUTO            ), '
     ', ''), 1, 1, ''))n OUTER APPLY(        SELECT[value2]=STUFF(REPLACE(REPLACE(            (                SELECT value2 FROM tb N2                WHERE N2.id = A.id                FOR XML AUTO            ), '
     ', ''), 1, 1, ''))n2

droptable tb

/*

id          values

----------- -----------

1           aa,bb

2           aaa,bbb,ccc

(2 行受影响)

*/