sql语法-表A如何由表B对应相同的值做转换
请教表A如何由表B对应相同的值做转换：
表A：
A1 (1) (2) (3) (1,2),(2,3) (1,2,3)
B1 (3) (6) (8) (3,6),(6,8) (3,6,8)
C1 (3) (6) (3,6)
…
表B：
(1) a
(2) b
(3) c
(6) d
(8) e
(1,2) f
(2,3) g
(3,6) h
(6,8) i
(1,2,3) j
(3,6,8) k
…
结果：
A1 a b c f,g j
B1 c d e h,i k
C1 c d h
[解决办法]
create function wsp(@c varchar(50))
returns varchar(50)
as
begin
if(len(@c)=0)
return ' '
else
begin
declare @star int
declare @end int
declare @s varchar(50)
declare @r varchar(50)
set @star=1
set @r= ' '
while charindex( '( ',@c)> 0
begin
set @s=substring(@c,1,charindex( ') ',@c))
select @r=@r+ ', '+c2 from t2 where t2.c1=@s
set @star=charindex( ') ',@c)+1
set @c=substring(@c,@star+1,len(@c))
end
set @r=right(@r,len(@r)-1)
end
return @r
end
select c1,dbo.wsp(c2) as c2,dbo.wsp(c3) as c3,dbo.wsp(c4) as c4,dbo.wsp(c5) as c25,dbo.wsp(c6) as c6 from t1
[解决办法]
--原始数据：@A
declare @A table(ID varchar(2),I1 varchar(3),I2 varchar(3),I3 varchar(5),I4 varchar(11),I5 varchar(100))
insert @A
select 'A1 ', '(1) ', '(2) ', '(3) ', '(1,2),(2,3) ', '(1,2,3),(2,3,4) ' union all
select 'B1 ', '(3) ', '(6) ', '(8) ', '(3,6),(6,8) ', '(3,6,8) ' union all
select 'C1 ', '(3) ', '(6) ', ' ', '(3,6) ', ' '
--原始数据：@B
declare @B table(I varchar(7),No varchar(1))
insert @B
select '(1) ', 'a ' union all
select '(2) ', 'b ' union all
select '(3) ', 'c ' union all
select '(6) ', 'd ' union all
select '(8) ', 'e ' union all
select '(1,2) ', 'f ' union all
select '(2,3) ', 'g ' union all
select '(3,6) ', 'h ' union all
select '(6,8) ', 'i ' union all
select '(1,2,3) ', 'j ' union all
select '(3,6,8) ', 'k ' union all
select '(2,3,4) ', 'l '--假设(2,3,4)为j
update a set a.I1=b.No from @A a join @B b on a.I1=b.I
update a set a.I2=b.No from @A a join @B b on a.I2=b.I
update a set a.I3=b.No from @A a join @B b on a.I3=b.I
-- I4是多个()的组合，这样处理
while exists (select 1 from @A a,@B b where charindex(b.I,a.I4)> 0)
update a set a.I4=replace(I4,b.I,b.No) from @A a join @B b on charindex(b.I,a.I4)> 0
-- I5也是？I4替换成I5
while exists (select 1 from @A a,@B b where charindex(b.I,a.I5)> 0)
update a set a.I5=replace(I5,b.I,b.No) from @A a join @B b on charindex(b.I,a.I5)> 0
select * from @A
/*
IDI1I2I3I4I5
A1abcf,gj,l
B1cdeh,ik
C1cdh
*/
