CODEWITHSUNDEEP
oracle-database
Brian
Brian

Reputation: 1387

Comma separated list of column names of a table

I am unable to get me a comma separated list of column names, given a table name. Any references are appreciated.

So my end result should be a sql statement in oracle which when run, will give me a comma separated list of column names in a given table. (so I can copy paste the list elsewhere)

edit:

So if my table looks like this: table1(fieidl1,field2,field3)

I am looking to get a string like "field1,field2,field3"

so I can plug in to a separate sql statement to select the fields from the table in that order.

Upvotes: 3

Views: 15038

Answers (5)

AnkitK
AnkitK

Reputation: 408

SELECT RTRIM(XMLAGG(XMLELEMENT(E,COLUMN_NAME,',').EXTRACT('//text()') ORDER BY COLUMN_NAME).GetClobVal(),',')
FROM USER_TAB_COLS WHERE TABLE_NAME = 'YOUR_TABLE_NAME'

Upvotes: 0

Thunder
Thunder

Reputation: 10986

This solution of mine works with old version of oracle as well.:)

with a as (
select rownum r,column_name from cols where table_name ='TableName')
,b as (
select SYS_CONNECT_BY_PATH(column_name, ',') Fields,rownum rn from a
start with r=1
CONNECT BY PRIOR r = r-1 ),c as (
select * from b order by 2 desc) select Fields from c where rownum =1

Upvotes: 0

Rajya lakshmi J
Rajya lakshmi J

Reputation: 31

select wm_concat(COLUMN_NAME) 
from ALL_TAB_COLUMNS 
where TABLE_NAME='MyTable';

Upvotes: 3

Aramillo
Aramillo

Reputation: 3216

You can use USER_TAB_COLS from oracle dictionary and LISTAGG like this:

SELECT LISTAGG (COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) 
FROM USER_TAB_COLS WHERE TABLE_NAME = 'YOUR_TABLE'

Upvotes: 13

Arun
Arun

Reputation: 951

I'm assuming your trying to export a query in *.csv format. If so, you can use the SPOOL command to do this.

SPOOL "C:\file\location\...\file.csv"
SELECT /*csv*/ * from your_table_name;
SPOOL OFF;

For more information, and examples look here. More examples.

Upvotes: 0

Related Questions