--创建用户定义函数
create function f_str(@ID)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select @r = @r + ','+str2 from 表b where 所属主表ID = @ID
set @r = stuff(@r,1,1,'')
return @r
end
Go--执行更新操作
update 表a set str1 = dbo.f_str(ID)
go
create function f_str(@ID)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select @r = @r + ','+str2 from 表b where 所属主表ID = @ID
set @r = stuff(@r,1,1,'')
return @r
end
Go--执行更新操作
update 表a set str1 = dbo.f_str(ID)
go
insert into a select 'a',null
insert into a select 'b',null
create table b(ID int,所属主表ID varchar(10),str2 varchar(10))
insert into b select 1,'a','张三'
insert into b select 2,'a','李四'
insert into b select 3,'a','王五'
insert into b select 4,'b','太阳'
insert into b select 5,'b','月亮'
go
--创建用户定义函数
create function f_getstr(@ID varchar(10))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select @r = @r + ','+str2 from b where 所属主表ID = @ID
set @r = stuff(@r,1,1,'')
return @r
end
Go--执行更新操作
update a set str1 = dbo.f_getstr(ID)
go
--查看更新结果
select * from a/*
ID str1
---- --------------
a 张三,李四,王五
b 太阳,月亮
*/
--删除测试数据
drop function f_getstr
drop table a,b