CREATE FUNCTION dbo.f_str(@orgid varchar(20)) RETURNS varchar(8000) AS BEGIN DECLARE @re varchar(8000) SET @re='' SELECT @re=@re+','+name FROM tb WHERE orgid=@orgid RETURN(STUFF(@re,1,1,'')) END
declare @sql varchar(8000) select @sql='' select @sql=@sql+','+[name] from humres where orgid='%%%%%%%%' select @sql=stuff(@sql,1,1,'') select @sql
select col from table结果集: 1 2 3 4 5我想要的是用逗号隔开的一个字符串 1,2,3,4,5最好是用SQL语句来实现我不想用游标来实现 100分奉上。谢谢。。 declare @output varchar(8000) select @output = coalesce(@output+',', '') + col from table print @output--如果col是数值型要转换一下: declare @output varchar(8000) select @output = coalesce(@output+',', '') + cast(col as varchar) from table print @outputdeclare @output varchar(8000)--输出系统表sysobjects的colid select @output = coalesce(@output+',', '') + cast(colid as varchar) from syscolumns where id = object_id('sysobjects') order by colid print @output /* 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25 */--输出系统表sysobjects的列名 set @output=null select @output = coalesce(@output+',', '') + name from syscolumns where id = object_id('sysobjects') order by colid print @output /* name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltrig,instrig,updtrig,seltrig,category,cache */ create table tb(id varchar(10),username varchar(10)) insert into tb values('1', '123') insert into tb values('2', '234') insert into tb values('3', 'hell') godeclare @a varchar(5000),@b varchar(5000) select @a='',@b='' select @a=@a+','+rtrim(id),@b=@b+','+username from tb select 'id',stuff(@a,1,1,'') union select 'username',stuff(@b,1,1,'') drop table tb/* -------- -------------- id 1,2,3 username 123,234,hell(所影响的行数为 2 行) */
RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000)
SET @re=''
SELECT @re=@re+','+name
FROM tb
WHERE orgid=@orgid
RETURN(STUFF(@re,1,1,''))
END
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+','+[name] from humres where orgid='%%%%%%%%'
select @sql=stuff(@sql,1,1,'')
select @sql
1
2
3
4
5我想要的是用逗号隔开的一个字符串
1,2,3,4,5最好是用SQL语句来实现我不想用游标来实现
100分奉上。谢谢。。
declare @output varchar(8000)
select @output = coalesce(@output+',', '') + col from table
print @output--如果col是数值型要转换一下:
declare @output varchar(8000)
select @output = coalesce(@output+',', '') + cast(col as varchar) from table
print @outputdeclare @output varchar(8000)--输出系统表sysobjects的colid
select @output = coalesce(@output+',', '') + cast(colid as varchar) from syscolumns where id = object_id('sysobjects') order by colid
print @output
/*
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
*/--输出系统表sysobjects的列名
set @output=null
select @output = coalesce(@output+',', '') + name from syscolumns where id = object_id('sysobjects') order by colid
print @output
/*
name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltrig,instrig,updtrig,seltrig,category,cache
*/
create table tb(id varchar(10),username varchar(10))
insert into tb values('1', '123')
insert into tb values('2', '234')
insert into tb values('3', 'hell')
godeclare @a varchar(5000),@b varchar(5000)
select @a='',@b=''
select @a=@a+','+rtrim(id),@b=@b+','+username from tb
select 'id',stuff(@a,1,1,'') union select 'username',stuff(@b,1,1,'')
drop table tb/*
-------- --------------
id 1,2,3
username 123,234,hell(所影响的行数为 2 行)
*/