新旧编码转换

[cdzch]

dzb表：
BH XBH
---------
01 1
02 2
03 3
04 4
05 5
06 6
07 7
08 8
09 9
10 A
11 B
12 C
13 D
14 E
15 F

tb表(每两位对应dzb中的一个bh)：
BH
01
01021011
0110
0111
1011


根据dzb，将tb表中的数据变成这样：
XBH
1
1,2,A,B
1,A
1,B
A,B

请各位大侠帮助。

[wlinglong]

declare @dzb TABLE (bh varchar(100),xbh varchar(100))
declare @str varchar(4000)

Insert INto @dzb select '01','A'
UNION  SELECT '02','B'
UNION SELECT '03','C'

declare @ls_str varchar(4000)
declare @i int
declare @j int

Select @str = '01010102020301'
    Set @i = len(@str) / 2
    Set @j = 0
    WHILE @j < 7
    BEGIN
        Select @ls_str = Isnull(xbh,'') +',' + Isnull(@ls_str,'') From @dzb where bh = SubString(@str,@j*2 + 1,2) 
        if @@error <> 0
        begin 
            break
        end
        Set @j = @j + 1
    END
SELECT Left(@LS_STR,Len(@ls_str) - 1)

[wlinglong]

declare @dzb TABLE (bh varchar(100),xbh varchar(100))
declare @str varchar(4000)

Insert INto @dzb select '01','A'
UNION  SELECT '02','B'
UNION SELECT '03','C'

declare @ls_str varchar(4000)
declare @i int
declare @j int

Select @str = '01010102020301'
    Set @i = len(@str) / 2
    Set @j = 0
    WHILE @j < @i
    BEGIN
        Select @ls_str = Isnull(xbh,'') +',' + Isnull(@ls_str,'') From @dzb where bh = SubString(@str,@j*2 + 1,2) 
        if @@error <> 0
        begin 
            break
        end
        Set @j = @j + 1
    END
SELECT Left(@LS_STR,Len(@ls_str) - 1)

[kk19840210]

create table dzb(bh varchar(10),xbh varchar(10))
insert into dzb values('01','1')
insert into dzb values('02','2')
insert into dzb values('03','3')
insert into dzb values('04','4')
insert into dzb values('05','5')
insert into dzb values('06','6')
insert into dzb values('07','7')
insert into dzb values('08','8')
insert into dzb values('09','9')
insert into dzb values('10','A')
insert into dzb values('11','B')
insert into dzb values('12','C')
insert into dzb values('13','D')
insert into dzb values('14','E')
insert into dzb values('15','F')

CREATE table tb(bh varchar(100))
insert into tb values('01')
insert into tb values('01021011')
insert into tb values('0110')
insert into tb values('0111')
insert into tb values('1011')

create function get_str(@bh varchar(100))
returns varchar(100)
as
begin
		declare @i int
		set @i=1
			while (@i<=len(@bh))
				begin
						set @bh=left(@bh,@i+1)+','+substring(@bh,@i+2,len(@bh))
						set @i=@i+3
				end
		select @bh=replace(@bh,bh,xbh) from dzb where charindex(bh,@bh)>0
			select @bh=left(@bh,len(@bh)-1)
		return @bh
end

select dbo.get_str(bh) from tb




1
1,2,A,B
1,A
1,B
A,B

(5 行受影响)

[wlinglong]

[Quote=引用 3 楼 kk19840210 的回复:]
SQL codecreate table dzb(bh varchar(10),xbh varchar(10))
insert into dzb values('01','1')
insert into dzb values('02','2')
insert into dzb values('03','3')
insert into dzb values('04','4')
insert into dzb values('05','5')
insert into dzb values('06','6')
insert into dzb values('07','7')
insert into dzb values('08','8')
insert into dzb values('09','9')
insert into dzb values('10','A')
…
[/Quote]

比我的好 少查询数据库很多次

[cdzch]

谢谢灵龙和飞天小虫！