SQL SERVER 中将重复记录合并为一条记录

合并列值
原著：邹建
改编：爱新觉罗.毓华  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
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)n
 OUTER APPLY(
        SELECT[value2]=STUFF(REPLACE(REPLACE(
            (
                SELECT value2 FROM tb N2
                WHERE N2.id = A.id
                FOR XML AUTO
            ), '<N2 value2="', ','), '"/>', ''), 1, 1, '')
)n2

droptable tb

/*
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc

(2 行受影响)
*/