if object_id('表a') is not null
drop table 表a
if object_id('表b') is not null
drop table 表b
if object_id('f_getstring') is not null
drop function f_getstring
go
create table 表a(出货日期 datetime,实际编号 varchar(10))
insert into 表a
select '2008-1-14','1' union all
select '2008-1-14','2' union all
select '2008-1-15','3'
create table 表b(出货日期 datetime,备注 varchar(10))
insert into 表b
select '2008-1-14',null union all
select '2008-1-15',null
go
create function f_getstring(@Out_dt datetime)
returns varchar(8000)
as
begin
declare cur cursor for select cast(实际编号 as varchar(10)) from 表a where 出货日期=@Out_dt
open cur
declare @s varchar(8000),@now varchar(10)
fetch next from cur into @now
while(@@fetch_status=0)
begin
set @s=isnull(@s,'')+@now+','
fetch next from cur into @now
end
close cur
deallocate cur
return substring(@s,1,len(@s)-1)
end
go
--如果只是查询出想要的结果
select 出货日期,dbo.f_getstring(出货日期) as 备注 from 表b
--真正的更新表b
update 表b set 备注=dbo.f_getstring(出货日期)