我两个表AA,BB
AA表结构为
col_htbh col_cgdh
H1 BB表结构为
col_htbh col_cgdh
H1 CG1
H1 CG2我如果得到结构把BB表的col_cgdh写到AA上去得到结果为
col_htbh col_cgdh
H1 CG1,CG2条件是AA.col_htbh=BB.col_htbh
AA表结构为
col_htbh col_cgdh
H1 BB表结构为
col_htbh col_cgdh
H1 CG1
H1 CG2我如果得到结构把BB表的col_cgdh写到AA上去得到结果为
col_htbh col_cgdh
H1 CG1,CG2条件是AA.col_htbh=BB.col_htbh
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(col_cgdh as varchar) from BB where col_htbh = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--更新语句
update aa set col_cgdh = bb.col_cgdh
from (select col_htbh,dbo.f_hb(col_htbh) as col_htbh from bb group by col_htbh)bb
where aa.col_htbh = bb.col_htbh
set @s=''
select @s=@s+','+quotename(col_cgdh)
from bb
order by col_htbh
set @s=stuff(@s,1,1,'')
exec('select '+@s+' from bb')
@col_htbh varchar(10)
)
returns varchar(200)
as
begin
declare @r varchar(200)
set @r=''
select @r=@r+','+rtrim(col_cgdh) from bb where col_htbh=@col_htbh
if @r<>''
set @r=stuff(@r,1,1,'')
return @r
end
go--调用
update aa
set col_cgdh=dbo.fn_col_cgdh(col_htbh)
insert dddd select 'H1',''
insert dddd select 'H2',''
create table eeee(col_htbh varchar(10),col_cgdh varchar(10))
insert eeee select 'H1', 'CG1'
insert eeee select 'H1', 'CG2'
insert eeee select 'H2', 'FF'go
create function getAB(@x varchar(10))
returns varchar(100)
begin
declare @s varchar(1000)
set @s=''
select @s=@s+col_cgdh +',' from eeee where col_htbh=@x
set @s=left(@s,len(@s)-1)
return @s
endupdate dddd set col_cgdh=dbo.getAB(col_htbh)