sql 合并行到列
(在sql server 2000中只能用函数解决。) --创建处理函数 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 dbo.f_str(@id int) returns varchar(8000) as begin declare @r varchar(8000) set @r = '' select @r = @r + ',' + value from tb where id=@id return stuff(@r, 1, 1, '') end go -- 调用函数 SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id drop table tb drop function dbo.f_str /* id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc （所影响的行数为2 行） */ --SQL2005中的方法 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 id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id （所影响的行数为2 行） /* id values ----------- -------------------- 1 aa,bb 2 aaa,bbb,ccc