declare @sql varchar(1000) set @sql='' select @sql=@sql+','+name from table1 where id in(1,2,3,4) select @sql=right(@sql,len(@sql)-1) exec ('select '+@sql)
select [name] from table1 where id in(1,2,3,4)
declare @str varchar(8000)select @str=case when @str is null then name else @str+','+name end from table1 where id in(1,2,3,4) select @str
declare @table1 table(id int ,name varchar(20))insert @table1 values(1,'s1') insert @table1 values(2,'s2') insert @table1 values(3,'s3') insert @table1 values(4,'s99') insert @table1 values(5,'sgg') insert @table1 values(6,'ttt') declare @sql varchar(1000) set @sql='' select @sql=@sql+','+name from @table1 where id in(1,2,3,4) select @sql=right(@sql,len(@sql)-1) select @sql-------------------------- s1,s2,s3,s99(所影响的行数为 1 行)
wzh1215(四脚蛇)的应该差不多了。
declare @sql varchar(1000) set @sql='' select @sql=@sql+','+name from table1 where id in(1,2,3,4) select @sql=right(@sql,len(@sql)-1) select @sql exec ('select '''+@sql+'''')
--通用一点的 --创建自定义函数实现合并create function f_merg(@idlist varchar(1000)) returns varchar(8000) as begin declare @re varchar(8000) select @re='',@idlist=','+@idlist+',' select @re=@re+','+name from table1 where charindex(','+cast(id as int)+',',@idlist)>) set @re=substring(@re,2,8000) return(@re) end go--调用: select dbo.f_merg('1,2,3,4')
--要改一下: --通用一点的 --创建自定义函数实现合并create function f_merg(@idlist varchar(1000)) returns varchar(8000) as begin declare @re varchar(8000) select @re='',@idlist=','+@idlist+',' select @re=@re+','+name from table1 where charindex(','+cast(id as varchar)+',',@idlist)>0 set @re=substring(@re,2,8000) return(@re) end go--调用: select dbo.f_merg('1,2,3,4')
--下面是测试--测试数据 create table table1(id int ,name varchar(20)) insert table1 select 1,'s1' union all select 2,'s2' union all select 3,'s3' union all select 4,'s99' union all select 5,'sgg' union all select 6,'ttt' go--创建自定义函数实现合并 create function f_merg(@idlist varchar(1000)) returns varchar(8000) as begin declare @re varchar(8000) select @re='',@idlist=','+@idlist+',' select @re=@re+','+name from table1 where charindex(','+cast(id as varchar)+',',@idlist)>0 set @re=substring(@re,2,8000) return(@re) end go--调用: select a=dbo.f_merg('1,2,3,4'),b=dbo.f_merg('1,3')go --删除测试 drop table table1 drop function f_merg/*--测试结果a b --------------- ------ s1,s2,s3,s99 s1,s3(所影响的行数为 1 行) --*/
declare @sql varchar(8000) set @sql='' select @sql=@sql+','+name from table1 where id in(1,2,3,4) select @sql=substring(@sql,2,8000) select @sql
set @sql=''
select @sql=@sql+','+name from table1 where id in(1,2,3,4)
select @sql=right(@sql,len(@sql)-1)
exec ('select '+@sql)
select @str
declare @table1 table(id int ,name varchar(20))insert @table1 values(1,'s1')
insert @table1 values(2,'s2')
insert @table1 values(3,'s3')
insert @table1 values(4,'s99')
insert @table1 values(5,'sgg')
insert @table1 values(6,'ttt')
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+name from @table1 where id in(1,2,3,4)
select @sql=right(@sql,len(@sql)-1)
select @sql--------------------------
s1,s2,s3,s99(所影响的行数为 1 行)
set @sql=''
select @sql=@sql+','+name from table1 where id in(1,2,3,4)
select @sql=right(@sql,len(@sql)-1)
select @sql
exec ('select '''+@sql+'''')
--创建自定义函数实现合并create function f_merg(@idlist varchar(1000))
returns varchar(8000)
as
begin
declare @re varchar(8000)
select @re='',@idlist=','+@idlist+','
select @re=@re+','+name from table1 where charindex(','+cast(id as int)+',',@idlist)>)
set @re=substring(@re,2,8000)
return(@re)
end
go--调用:
select dbo.f_merg('1,2,3,4')
--通用一点的
--创建自定义函数实现合并create function f_merg(@idlist varchar(1000))
returns varchar(8000)
as
begin
declare @re varchar(8000)
select @re='',@idlist=','+@idlist+','
select @re=@re+','+name from table1
where charindex(','+cast(id as varchar)+',',@idlist)>0
set @re=substring(@re,2,8000)
return(@re)
end
go--调用:
select dbo.f_merg('1,2,3,4')
create table table1(id int ,name varchar(20))
insert table1
select 1,'s1'
union all select 2,'s2'
union all select 3,'s3'
union all select 4,'s99'
union all select 5,'sgg'
union all select 6,'ttt'
go--创建自定义函数实现合并
create function f_merg(@idlist varchar(1000))
returns varchar(8000)
as
begin
declare @re varchar(8000)
select @re='',@idlist=','+@idlist+','
select @re=@re+','+name from table1
where charindex(','+cast(id as varchar)+',',@idlist)>0
set @re=substring(@re,2,8000)
return(@re)
end
go--调用:
select a=dbo.f_merg('1,2,3,4'),b=dbo.f_merg('1,3')go
--删除测试
drop table table1
drop function f_merg/*--测试结果a b
--------------- ------
s1,s2,s3,s99 s1,s3(所影响的行数为 1 行)
--*/
set @sql=''
select @sql=@sql+','+name from table1 where id in(1,2,3,4)
select @sql=substring(@sql,2,8000)
select @sql