What is the sql command for Sybase IQ to get the List of columns from a table separated with a delimeter. We use this below command in oracle

Question

Below is the command I am using in SQL developer and that result is 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';

Answer 1

Sybase (now SAP) IQ is built on top of (or uses as a front-end) the Sybase (also now SAP) SQLAnywhere database engine; net result is that, depending on what you're trying to do, you can use the SQL constructs (eg, functions) from both products.

NOTE: I don't have a IQ db in front of me so you may need to tweak the following ...

SQLAnywhere has a list() function that works similar to Oracle's listagg().

Here are a few examples of displaying the columns (as a comma-delimited list) from the SYSTABLE table:

# 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