oracle11g - How to transpose column into row in oracle sql 11G

Question

I need to convert column into row for the below select column_name from all_tab_cols where table_name='TABLE_NAME' ;

COLUMN_1
COLUMN_2
COLUMN_3
COLUMN_4
COLUMN_5
COLUMN_6
COLUMN_7

Tried using pivot operator/clause ,

i need to mention all the column names for the table if the table contain more number of column it wouldnt be possible to mention in the pivot function in in clause,

select * from
(
    select column_name
    from all_tab_cols
    where table_name = 'TABLE_NAME'
)
pivot ( min(column_name) for column_name in 
(
'COLUMN_1', 'COLUMN_2', 'COLUMN_3', 'COLUMN_4', 'COLUMN_5', 'COLUMN_6', 'COLUMN_7'
));

Expected output:

COLUMN_1    COLUMN_2    COLUMN_3    COLUMN_4    COLUMN_5    COLUMN_6    COLUMN_7

Could anyone Please advise how to convert column into rows

Answer 1

Try this:

select listagg(A,'  ') within group (order by A) as Names
from test

In ur case the query goes like:

select listagg(column_name,'  ')  within group (order by column_name) as column_name
from all_tab_cols 
where table_name='TABLE_NAME' ;