不用游标的字符串字段合并
参考下面的贴子
http://expert.csdn.net/Expert/topicview.asp?id=632928
修改后,解决原贴子的遗留问题。
set nocount on
if object_id('tempdb.dbo.#YourResultTable') is not null drop table #YourResultTable
create table #YourResultTable (id1 int,id2 varchar(5), value varchar(10))
insert #YourResultTable (id1,id2,value) values(8,'a','nice')
insert #YourResultTable (id1,id2,value) values(8,'a','niiice')
insert #YourResultTable (id1,id2,value) values(8,'a','ice')
insert #YourResultTable (id1,id2,value) values(1,'e','cool')
insert #YourResultTable (id1,id2,value) values(1,'e','nice')
insert #YourResultTable (id1,id2,value) values(1,'b','wow')
insert #YourResultTable (id1,id2,value) values(1,'b','wodw')
insert #YourResultTable (id1,id2,value) values(2,'a','cool')
insert #YourResultTable (id1,id2,value) values(2,'a','wow')
insert #YourResultTable (id1,id2,value) values(3,'c','cool')
insert #YourResultTable (id1,id2,value) values(3,'c','nice')
insert #YourResultTable (id1,id2,value) values(4,'a','nice')
select * from #YourResultTable
declare @z varchar(100),@id1 int,@id2 varchar(5)
if object_id('tempdb.dbo.#ProcessTable') is not null drop table #ProcessTable
create table #ProcessTable (id1 int,id2 varchar(5),value varchar(100), ProcessStep int identity(1,1))
insert #ProcessTable(id1,id2,value)
select id1,id2, value
from #YourResultTable
order by id1,id2,value
update #ProcessTable
set @z = value = case when @id1=id1 and @id2=id2 then @z+',' else '' end + value
,@id1 = id1
,@id2 = id2
from #ProcessTable
--select * from #ProcessTable
select id1,id2, value
from (select max(ProcessStep) MaxStep from #ProcessTable group by id1,id2) x
join #ProcessTable t
on ProcessStep = MaxStep
--在 MS SQL Server 2000 下通过。