Oracle - How to write Query to return column header name with pipe delimited?

Question

I want a Query to return the header name with pipe delimited. I found some reference online but couldn't get exactly what I want.

I want something return like this, not need to output for a file, just a query result:

column1|column2|column3|column4|.......|columnN

I tried this: SELECT /delimited/ from tableName where rownum<1; Run as script using F5.

"UPC","UPC_DESCRIPTION","ITEM_CODE","DEPT_CODE"

But the result is comma delimited and has the quotes enclosure. --I don't want it.

In fact I have so many columns, is there a easy way/simple query to return ALL header (pipe delimited) without manually type each?

Thanks for any help!!

Answer 1

Can't you just do:

select col1 || '|' || col2 || '|' || col3 || '|' || col4 || '|' || col5
  from table;

You can produce your query:

select 'select ' || LISTAGG(column_name , ' || ''|'' || ') within group (order by column_id) || ' from my_table' 
  from user_tab_columns 
  where table_name = 'MY_TABLE';

If you would like to get only header for your data please do:

select LISTAGG(column_name, '|') within group (order by column_id)
      from user_tab_columns 
      where table_name = 'MY_TABLE';