下面是我在SQL developer
中使用的命令,结果是Like Col1,Col2,Col3,..n
select listagg(COLUMN_NAME, ', ') within group (order by column_id asc)
from all_tab_columns
where table_name = 'NEIGHBORHOOD_ZIP_IQ';
Sybase(现在的SAP(IQ建立在Sybase(现在也是SAP(SQLAnywhere数据库引擎之上(或用作前端(;最终结果是,根据您要执行的操作,您可以使用两种产品的SQL构造(例如,函数(。
注意:我面前没有 IQ db,因此您可能需要调整以下内容......
SQLAnywhere有一个类似于Oraclelistagg()
的list()
函数。
下面是显示 SYSTABLE 表中的列(以逗号分隔的列表形式(的几个示例:
# all of the following examples use the comma (',') as the delimiter
# output determined by order in which column names are pulled from table (probably column_id)
select list(c.column_name,',')
from SYSTABLE t
join SYSCOLUMN c
on t.table_id = c.table_id
where t.table_name = 'SYSTABLE'
go
table_id,file_id,count,first_page,last_page,primary_root,creator,first_ext_page,last_ext_page,table_page_count,ext_page_count,object_id,table_name,table_type,view_def,remarks,replicate,existing_obj,remote_location,remote_objtype,srvid,server_type,primary_hash_limit,page_map_start,source,encrypted,location_escape_char
# order the output by column_name
select list(c.column_name order by c.column_name,',')
from SYSTABLE t
join SYSCOLUMN c
on t.table_id = c.table_id
where t.table_name = 'SYSTABLE'
go
count,creator,encrypted,existing_obj,ext_page_count,file_id,first_ext_page,first_page,last_ext_page,last_page,location_escape_char,object_id,page_map_start,primary_hash_limit,primary_root,remarks,remote_location,remote_objtype,replicate,server_type,source,srvid,table_id,table_name,table_page_count,table_type,view_def
# order the output by column_id
select list(c.column_name order by c.column_id,',')
from SYSTABLE t
join SYSCOLUMN c
on t.table_id = c.table_id
where t.table_name = 'SYSTABLE'
go
table_id,file_id,count,first_page,last_page,primary_root,creator,first_ext_page,last_ext_page,table_page_count,ext_page_count,object_id,table_name,table_type,view_def,remarks,replicate,existing_obj,remote_location,remote_objtype,srvid,server_type,primary_hash_limit,page_map_start,source,encrypted,location_escape_char