如何实现以下SQL功能,谢谢 比如我有一个SQL:select Head_number from TB得到结果为:0001000200030004如何将上面的结果并起来得到:0001000200030004谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select col from table结果集:12345我想要的是用逗号隔开的一个字符串1,2,3,4,5最好是用SQL语句来实现我不想用游标来实现100分奉上。谢谢。。declare @output varchar(8000)select @output = coalesce(@output+',', '') + col from tableprint @output--如果col是数值型要转换一下:declare @output varchar(8000)select @output = coalesce(@output+',', '') + cast(col as varchar) from tableprint @outputdeclare @output varchar(8000)--输出系统表sysobjects的colidselect @output = coalesce(@output+',', '') + cast(colid as varchar) from syscolumns where id = object_id('sysobjects') order by colidprint @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=nullselect @output = coalesce(@output+',', '') + name from syscolumns where id = object_id('sysobjects') order by colidprint @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 tbselect 'id',stuff(@a,1,1,'') union select 'username',stuff(@b,1,1,'') drop table tb/*-------- --------------id 1,2,3username 123,234,hell(所影响的行数为 2 行)*/ create or replace function fff is v_id varchar2(100); begin for c1 in (select id from table ) loop v_id:=v_id||c1.id; end loop; end; oracle锁的问题 insert into 子查询问题 为什么这个程序只能插入GIF图片? Oracle 中 JDBC ( oci 和 thin ) 的优缺点 和 特点是什么? 触发器无效且未通过重新确认 报表是个啥东西请介绍一下,那有这方面的资料?谢谢! 超级菜鸟问题:D2000是什么? 触发器为什么会失效?怎样自动编译失败的触发器? 给出数据库所有表中名叫"test"字段出现的个数的SQL语句怎么个写法? Oracle 权限管理问题 游标变量不能在包里面定义? 请问在pl/sql developer怎样打印变量
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 行)
*/
is
v_id varchar2(100);
begin
for c1 in (select id from table ) loop
v_id:=v_id||c1.id;
end loop;
end;