合并列值

来源：互联网发布：2017网络舆情编辑：程序博客网时间：2024/06/06 02:49
合并列值原著：邹建改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳表结构，数据如下：id    value----- ------1     aa1     bb2     aaa2     bbb2     ccc需要得到结果：id     values------ -----------1      aa,bb2      aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）1. 旧的解决方法(在sql server 2000中只能用函数解决。)--1. 创建处理函数create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goCREATE 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, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tbdrop function dbo.f_str/*id          value      ----------- -----------1           aa,bb2           aaa,bbb,ccc（所影响的行数为 2 行）*/--2、另外一种函数.create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')go--创建一个合并的函数create function f_hb(@id int)returns varchar(8000)asbegin  declare @str varchar(8000)  set @str = ''  select @str = @str + ',' + cast(value as varchar) from tb where id = @id  set @str = right(@str , len(@str) - 1)  return(@str)Endgo--调用自定义函数得到结果：select distinct id ,dbo.f_hb(id) as value from tbdrop table tbdrop function dbo.f_hb/*id          value      ----------- -----------1           aa,bb2           aaa,bbb,ccc（所影响的行数为 2 行）*/2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')go-- 查询处理SELECT * 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            ), '<N value="', ','), '"/>', ''), 1, 1, ''))Ndrop table tb/*id          values----------- -----------1           aa,bb2           aaa,bbb,ccc(2 行受影响)*/--SQL2005中的方法2create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')from tbgroup by id/*id          values----------- --------------------1           aa,bb2           aaa,bbb,ccc(2 row(s) affected)*/drop table tb
0 0