更正:tab2:
SellID CompanyPropertyIDS
A001 01;02;
A001 01
A001 01;03;
A001 01;02;03;
想要得到結果為:
SellID CompanyPropertyName
A001 aaa;bbb
A001 aaa
A001 aaa;ccc
A001 aaa;bbb;ccc
SellID CompanyPropertyIDS
A001 01;02;
A001 01
A001 01;03;
A001 01;02;03;
想要得到結果為:
SellID CompanyPropertyName
A001 aaa;bbb
A001 aaa
A001 aaa;ccc
A001 aaa;bbb;ccc
Insert into tab1
select '01','aaa'
union all select '02','bbb'
union all select '03','ccc'create table tab2(SellID varchar(10),CompanyPropertyIDS varchar(100))
Insert into tab2
select 'A001','01;02;'
union all select 'A001','01'
union all select 'A001','01;03;'
union all select 'A001','01;02;03;'select * from tab1
select * from tab2--創建函數處理
create function dbo.fn_f(@s varchar(1000))
returns varchar(1000)
as
begin
declare @a varchar(1000),@b varchar(1000)
declare @i int
set @a=''
set @i=1
while @i>0
begin
select @a=@a+CompanyPropertyName+';' from tab1 where CompanyPropertyID=substring(@s,@i,2)
set @i=charindex(';',@s,@i+3)-2
end
return(left(@a,len(@a)-1))
end--刪除
drop table tab1
drop table tab2
drop function dbo.fn_f--結果
select SellID,CompanyPropertyName=dbo.fn_f(CompanyPropertyIDS) from tab2
SellID CompanyPropertyName
---------------------------------------------
A001 aaa;bbb
A001 aaa
A001 aaa;ccc
A001 aaa;bbb;ccc
CompanyPropertyID varchar(10),
CompanyPropertyName varchar(10)
)
go
insert tab1
select
'01', 'aaa'
union all select
'02', 'bbb'
union all select
'03', 'ccc'
gocreate table tab2(
SellID varchar(10),
CompanyPropertyIDS varchar(30)
)
go
insert tab2
select
'A001', '01;02'
union all select
'A002', '01'
union all select
'A003', '01;03'
union all select
'A004', '01;02;03'
go
while exists (
select 1 from tab1,tab2
where charindex(';'+tab1.CompanyPropertyID+';',';'+tab2.CompanyPropertyIDS+';')>0
)
update tab2
set CompanyPropertyIDS=stuff(CompanyPropertyIDS,charindex(';'+tab1.CompanyPropertyID+';',';'+tab2.CompanyPropertyIDS+';'),len(tab1.CompanyPropertyID),tab1.CompanyPropertyName)
from tab1
where charindex(';'+tab1.CompanyPropertyID+';',';'+tab2.CompanyPropertyIDS+';')>0select * from tab2
create function dbo.fn_f(@s varchar(1000))
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=@s
while exists (
select 1 from tab1
where charindex(';'+tab1.CompanyPropertyID+';',';'+@a+';')>0
) select @a=stuff(@a,charindex(';'+tab1.CompanyPropertyID+';',';'+@a+';'),len(tab1.CompanyPropertyID),tab1.CompanyPropertyName)
from tab1
where charindex(';'+tab1.CompanyPropertyID+';',';'+@a+';')>0
return @a
end
drop table [dbo].[table_1]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table_2]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_nameFind]') and OBJECTPROPERTY(id, N'IsTableFunction') = 1)
drop function [dbo].[fn_nameFind]goCREATE FUNCTION fn_nameFind()
RETURNS @nameFind TABLE (SellID varchar(10) not null ,CompanyPropertyIDS varchar(20) not null,findname varchar(20) null)
AS
BEGIN
insert into @nameFind select *,CompanyPropertyIDS from table_2 while @@rowcount>0
update @nameFind set findname=replace(findname,b.CompanyPropertyID,b.CompanyPropertyName) from @nameFind a join table_1 b on a.findname like '%'+b.CompanyPropertyID+'%'return
ENDgo
create table table_1(CompanyPropertyID varchar(10) not null primary key ,CompanyPropertyName varchar(20) not null)create table table_2(SellID varchar(10) not null ,CompanyPropertyIDS varchar(20) not null)goinsert into table_1
select '01','aaa' union all
select '02','bbb' union all
select '03','ccc'
insert into table_2
select 'A001','01;02' union all
select 'A001','01' union all
select 'A001','01;03' union all
select 'A001','01;02;03' goselect * from table_1
/*
CompanyPropertyID CompanyPropertyName
01 aaa
02 bbb
03 ccc
*/select * from table_2
/*
SellID CompanyPropertyIDS
A001 01;02
A001 01
A001 01;03
A001 01;02;03*/select a.SellID,b.findname CompanyPropertyName from table_2 a inner join fn_nameFind() b on a.SellID=b.SellID and a.CompanyPropertyIDS=b.CompanyPropertyIDS
/*
SellID CompanyPropertyName
A001 aaa;bbb;
A001 aaa
A001 aaa;ccc;
A001 aaa;bbb;ccc
*/