如何合并同一个人的多条记录

[jimsons]

数据库的结构如下:

no item
01 AA
01 BB
02 CC
02 DD
02 EE
03 FF
04 GG
04 HH

希望将no相同的列整合为一条记录如下
no items
01 AA,BB
02 CC,DD,EE
03 FF
04 GG,HH

我现在用游标来实现,判断一条记录是否与前一条记录相同,相同的话就合并item字段,虽然能够实现,但效率很低,不知大家有没有更好的解决办法
谢谢

[xeqtr1982]

create table tb(no int,item varchar(10))
insert into tb
select '01', 'AA'
union all select '01', 'BB'
union all select'02', 'CC'
union all select'02', 'DD'
union all select'02', 'EE'
union all select'03', 'FF'
union all select'04', 'GG'
union all select'04', 'HH'
go

create function dbo.fc_str(@no varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(item as varchar(100)) from tb where no=@no
return stuff(@sql,1,1,'')
end
go

select no,dbo.fc_str(no) as item from tb group by no

drop table tb

drop function dbo.fc_str

[jimsons]

但是我有40万条记录,用你的办法怎么解决

[jimsons]

我明白你意思了,我试试看...

[aniude]

create function show(@no varchar(4))
returns varchar(100)
as
begin
declare @back varchar(100)
set @back=''
select @back=@back+items+',' from tb where no=@no
return(left(@back,len(@back)-1))
end
select no from tb group by no

[jimsons]

解决了,谢谢