declare @s varchar(8000) declare @tid int declare @tname varchar(20) select @s = '1,2,3,4,5' create table #t(id int identity(1,1),name varchar(20) null) insert into #t select '赵' union all select '钱' union all select '孙' union all select '李' union all select '周' declare c_1 cursor for select id,name from #t open c_1 fetch next from c_1 into @tid,@tname while @@fetch_status = 0 begin select @s = replace(@s,cast(@tid as varchar(10)),cast(@tid as varchar(10))+@tname) fetch next from c_1 into @tid,@tname end select @s drop table #t
close c_1 deallocate c_1
create table t1(id varchar(100)) insert into t1 select '1,2,3,4,5' create table t2(id int,name varchar(10)) insert into t2 select 1,'赵' insert into t2 select 2,'钱' insert into t2 select 3,'孙' gocreate function f_str(@id varchar(1000)) returns varchar(1000) as begin set @id=','+@id+',' select @id=replace(@id,','+rtrim(id)+',',','+rtrim(id)+name+',') from t2 set @id=substring(@id,2,len(@id)-2) return @id end goselect dbo.f_str(id) as name from t1 /* name ----------------------- 1赵,2钱,3孙,4,5 */drop function f_str drop table t1,t2
谢谢楼上的兄弟们.请注意,我说,只能用一个SQL语句来写,不可以写存储过程.
create table A ( A1 varchar(10) )create table B ( id int, name varchar(5) )insert A select '1,2,3'insert B select 1,'赵' insert B select 2,'钱' insert B select 3,'孙' create Function F_T(@A1 varchar(10)) returns varchar(20) as begin declare @T varchar(20) declare @i int declare @id int set @i=1 set @T='' while @i<=len(@A1) begin if substring(@A1,@i,1)<>',' begin select @T=@T+ substring(@A1,@i,1) +name from B where id=cast(substring(@A1,@i,1) as int) end else select @T=@T+substring(@A1,@i,1) set @i=@i+1 end return @T endselect dbo.F_T(A1) from A
declare @tid int
declare @tname varchar(20)
select @s = '1,2,3,4,5'
create table #t(id int identity(1,1),name varchar(20) null)
insert into #t
select '赵' union all
select '钱' union all
select '孙' union all
select '李' union all
select '周'
declare c_1 cursor for select id,name from #t
open c_1
fetch next from c_1 into @tid,@tname
while @@fetch_status = 0
begin
select @s = replace(@s,cast(@tid as varchar(10)),cast(@tid as varchar(10))+@tname)
fetch next from c_1 into @tid,@tname
end
select @s
drop table #t
deallocate c_1
insert into t1 select '1,2,3,4,5'
create table t2(id int,name varchar(10))
insert into t2 select 1,'赵'
insert into t2 select 2,'钱'
insert into t2 select 3,'孙'
gocreate function f_str(@id varchar(1000))
returns varchar(1000)
as
begin
set @id=','+@id+','
select @id=replace(@id,','+rtrim(id)+',',','+rtrim(id)+name+',') from t2
set @id=substring(@id,2,len(@id)-2)
return @id
end
goselect dbo.f_str(id) as name from t1
/*
name
-----------------------
1赵,2钱,3孙,4,5
*/drop function f_str
drop table t1,t2
create table A
(
A1 varchar(10)
)create table B
(
id int,
name varchar(5)
)insert A select '1,2,3'insert B select 1,'赵'
insert B select 2,'钱'
insert B select 3,'孙'
create Function F_T(@A1 varchar(10))
returns varchar(20)
as
begin
declare @T varchar(20)
declare @i int
declare @id int
set @i=1
set @T=''
while @i<=len(@A1)
begin
if substring(@A1,@i,1)<>','
begin
select @T=@T+ substring(@A1,@i,1) +name from B where id=cast(substring(@A1,@i,1) as int)
end
else
select @T=@T+substring(@A1,@i,1) set @i=@i+1
end
return @T
endselect dbo.F_T(A1) from A