再谈字符串合并问题(类似SUM(字符串字段))
飘香曾经对此问题给出一种解答方法:
http://expert.csdn.net/Expert/topic/632/632928.xml?temp=.8559076
后来,xuzhenhua21 (舍得) 又对该问题开了个讨论贴:
http://expert.csdn.net/Expert/topic/1309/1309227.xml?temp=.1356012
这个问题其实也曾让我苦思冥想过很长时间,最好的办法当然是希望mssql xp加入这种语法;但另一方面,本着没有路我们也要创造路的原则,所以这里提供另一种实现的思路:
--设表名为b,分组的字段名为gid(int),字符串字段为str(varchar(100)),同时b表的主关键字字段为id(int)
set nocount on
declare @imax int,@i int,@sql varchar(8000)
set @imax = 0
set @i = 0
select top 1 @imax = count(*) from b group by gid order by count(*) desc
select gid into #t from b group by gid order by gid
while @i < @imax
begin
set @sql = 'alter table #t add value' + convert(varchar,@i+1) + ' varchar(100) null'
exec(@sql)
set @sql = 'update m set m.value' + convert(varchar,@i+1) + '=n.value from #t as m,
(select b2.value,min(b2.gid) as gid from b as b1,b as b2
where b1.gid in (select gid from b group by gid having count(*) >' + convert(varchar,@i) + ')
and b1.gid = b2.gid and b1.id <= b2.id
group by b2.value
having count(*) = ' + convert(varchar,@i+1) +
') as n where m.gid = n.gid'
exec(@sql)
set @i = @i + 1
end
select * from #t
drop table #t
set nocount off
--表构造语句
create table b(id int,gid int,value varchar(100))
insert b values(1,3,'river')
insert b values(2,1,'mother')
insert b values(3,1,'C++')
insert b values(4,2,'love')
insert b values(5,1,'hate')
insert b values(6,3,'gaoding')
开这个帖子是为了抛砖引玉,希望还有其他更好的办法提供!谢谢!