語句多了些,不過能達到你的要求.declare @v_phone varchar(100)
declare @v_id varchar(20)
set @v_phone = ''
select * into #temp1 from tabble where 1 = 2;
go
insert into #temp1 (id)
select id from table;
go
declare mycursor cursor for select id from #temp1
open mycursor
fetch mycursor into @v_id
while @@fetch_status = 0
begin
select @v_phone = @v_phone+','+phone from
tabble where id = @v_id
set @v_phone = substring(@v_phone,2,len(@v_phone))
update #temp1 set phone = @v_phone where id = @v_id
end
close mycursor
deallocate mycursor
go
select * from #temp1
declare @v_id varchar(20)
set @v_phone = ''
select * into #temp1 from tabble where 1 = 2;
go
insert into #temp1 (id)
select id from table;
go
declare mycursor cursor for select id from #temp1
open mycursor
fetch mycursor into @v_id
while @@fetch_status = 0
begin
select @v_phone = @v_phone+','+phone from
tabble where id = @v_id
set @v_phone = substring(@v_phone,2,len(@v_phone))
update #temp1 set phone = @v_phone where id = @v_id
end
close mycursor
deallocate mycursor
go
select * from #temp1
我的意思是有没有不用游标的方法来实现。
这里如果 B表有ID和PHONE都相同的记录,则只出现一次create table #Temp
(
id varchar(20) not null,
phone varchar(400) /*如果SQLSERVER 7.0以下,只能有255个字符*/
)select * into #Temp1 from b
WHILE exists(select * from #Temp1)
begin
select id,min(phone) as phone
into #Temp2
from b group by id
insert #Temp
select * from #Temp2
delete #Temp1
from #Temp1,#Temp2
where #Temp1.id=#Temp2.id
and #Temp1.phone=#Temp2.phone
drop table #Temp2
enddrop Table #Temp1
select * from #Temp
drop Table #Temp
谢谢Haiwer!给你15分吧,我也算把分给出了,我认为将Haiwer的方法作成存储过程也就基本实现了我的要求。
我最近才到这里,看到气氛不错,也试一试如何给分。谢谢了。我认为这个问题有待近一步讨论,如果谁有更好的方案,我将加分继续给。