sql语句 对号入座

[naner_china]

create table #tb(f1 varchar(100))
insert into #tb
select '5,8,9,10,13' union all
select '5,8,10,12,13'union all
select '5,9,10,12,13'union all
select '6,7,8,9,11' union all
select '1,3,4,5,11' union all
select '2,4,5,6,7' union all
select '1,3,5,7,8'


给定一个字符串如下
A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S
要求按照#tb表中f1字段的值对字符串相应位置取值，各个值以空格分开
对应关系：
比如'5,8,9,10,13' 取字符串A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S 第5,8,9,10,13个位置的对应值 结果 9 Y - X 0 （值与值间加空格）

上面字符串的最后结果如下：
9 Y - X 0
9 Y X + 0
9 - X + 0
2 M Y - .
A D 5 9 .
A D 9 M Y
请用sql2000实现

[liangCK]

跟着刚刚的那个思想不就行了吗？

[牙签是竹子的]

[naner_china]

sql2005语法我不熟悉
看不懂
liangCK
帮写个sql2000的吧

[htl258_Tony]

两贴一样的,小梁搞定了,但要在2005的环境下运行就是了.

[dawugui]

--用个函数来解决.

create table tb(f1 varchar(100)) 
insert into tb 
select '5,8,9,10,13' union all 
select '5,8,10,12,13'union all 
select '5,9,10,12,13'union all 
select '6,7,8,9,11'  union all 
select '1,3,4,5,11'  union all 
select '2,4,5,6,7'  union all 
select '1,3,5,7,8' 
go

create function my_func (@f1 varchar(50))
RETURNS varchar(50)
AS
BEGIN
   declare @str varchar(50)
   declare @rtn varchar(50)
   declare @i as int
   set @str = 'A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S'
   set @rtn = ''
   set @i = 0
   while charindex(',',@f1) > 0
   begin
      set @i = left(@f1 , charindex(',',@f1) - 1)
      set @rtn = @rtn + ' ' + substring(@str , (@i-1)*2+1 , 1)
      set @f1 = substring(@f1 , charindex(',',@f1) + 1 , len(@f1))
   end
   set @i = @f1
   set @rtn = @rtn + ' ' + substring(@str , (@i-1)*2+1 , 1)
   RETURN(@rtn)
END
go

select f1 , f2 = dbo.my_func(f1) from tb

drop table tb
drop function dbo.my_func

/*
f1                                                                                                   f2                                                 
---------------------------------------------------------------------------------------------------- -------------------------------------------------- 
5,8,9,10,13                                                                                           9 Y - X 0
5,8,10,12,13                                                                                          9 Y X + 0
5,9,10,12,13                                                                                          9 - X + 0
6,7,8,9,11                                                                                            2 M Y - .
1,3,4,5,11                                                                                            A D 5 9 .
2,4,5,6,7                                                                                             N 5 9 2 M
1,3,5,7,8                                                                                             A D 9 M Y

（所影响的行数为 7 行）

*/

[dawugui]

小梁那个太复杂了.

[肥龙上天]

if object_id('ta') is not null drop table ta
go
create table ta(id int identity(1,1),dd varchar(1))
insert ta values('A')
insert ta values('N')
insert ta values('D')
insert ta values('5')
insert ta values('9')
insert ta values('2')
insert ta values('M')
insert ta values('Y')
insert ta values('-')
insert ta values('X')
insert ta values('.')
insert ta values('+')
insert ta values('0')
insert ta values('4')
insert ta values('S')
go
select * from ta
----创建函数
create function fn_getString(@str varchar(50))
returns varchar(50)
as
begin
	declare @R varchar(50)
	select @R = isnull(@R,'')+dd from ta where charindex(','+rtrim(id)+',',','+@str+',')>0
	return @R
end

select *,rr = dbo.fn_getString(f1) from #tb
/*
f1                                                                                                   rr
---------------------------------------------------------------------------------------------------- --------------------------------------------------
5,8,9,10,13                                                                                          9Y-X0
5,8,10,12,13                                                                                         9YX+0
5,9,10,12,13                                                                                         9-X+0
6,7,8,9,11                                                                                           2MY-.
1,3,4,5,11                                                                                           AD59.
2,4,5,6,7                                                                                            N592M
1,3,5,7,8                                                                                            AD9MY

(7 row(s) affected)
*/

[naner_china]

正解 谢谢 结贴

[肥龙上天]

----创建函数
alter function fn_getString(@str varchar(50))
returns varchar(50)
as
begin
	declare @R varchar(50)
	select @R = isnull(@R,'')+dd+' ' from ta where charindex(','+rtrim(id)+',',','+@str+',')>0
	return @R
end

select *,rr = dbo.fn_getString(f1) from #tb
/*
f1                                                                                                   rr
f1                                                                                                   rr
---------------------------------------------------------------------------------------------------- --------------------------------------------------
5,8,9,10,13                                                                                          9 Y - X 0 
5,8,10,12,13                                                                                         9 Y X + 0 
5,9,10,12,13                                                                                         9 - X + 0 
6,7,8,9,11                                                                                           2 M Y - . 
1,3,4,5,11                                                                                           A D 5 9 . 
2,4,5,6,7                                                                                            N 5 9 2 M 
1,3,5,7,8                                                                                            A D 9 M Y 

(7 row(s) affected)

*/

[liangCK]

CREATE FUNCTION dbo.getdata(@s1 VARCHAR(50),@s2 VARCHAR(50))
    RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @t TABLE(id int identity,data VARCHAR(10))
    DECLARE @result VARCHAR(50)
    INSERT @t(data)
        SELECT
            SUBSTRING(@s2,number,CHARINDEX(',',@s2+',',number)-number)
        FROM master.dbo.spt_values
        WHERE type='p'
            AND number BETWEEN 1 AND LEN(@s2)
            AND SUBSTRING(','+@s2,number,1)=','
    
    SET @result=''
    SELECT
        @result=@result+' '+data
    FROM @t
    WHERE CHARINDEX(','+RTRIM(id)+',',','+@s1+',')>0
    
    RETURN STUFF(@result,1,1,'');
END
GO

create table #tb(f1 varchar(100)) 
insert into #tb 
select '5,8,9,10,13' union all 
select '5,8,10,12,13'union all 
select '5,9,10,12,13'union all 
select '6,7,8,9,11'  union all 
select '1,3,4,5,11'  union all 
select '2,4,5,6,7'  union all 
select '1,3,5,7,8' 

SELECT
    f1,
    dbo.getdata(f1,'A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S')
FROM #tb

GO
DROP TABLE #tb
DROP FUNCTION dbo.getdata