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

are you want to create pivot column dynamic ?

Sanjay Radadiya
– Sanjay Radadiya

2016-07-29 05:49:16 +00:00
Commented Jul 29, 2016 at 5:49 — Sanjay Radadiya
– Sanjay Radadiya, Commented Jul 29, 2016 at 5:49

XING · Accepted Answer · 2016-07-29 07:21:06Z

3

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' ;

answered Jul 29, 2016 at 7:21

XING

9,9164 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sanjay Radadiya · Accepted Answer · 2016-07-29 07:39:10Z

1

after so much time on googling

i found that pivot query haven't any dynamic features

so after i found solution at here

https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

here download pivot function from here

http://paste.ubuntu.com/21378705/

run this script and this script automatically create pivot function

with the use of this function we can create dynamic column from rows.

Example :

    select * from table( pivot(  Q'$ select column_name,column_name name 
    from all_tab_cols where table_name = 'TABLE_NAME' $') )

i hope this will help.

answered Jul 29, 2016 at 7:39

Sanjay Radadiya

1,29615 silver badges24 bronze badges

2 Comments

user3016075 Over a year ago

Unable to use this in my project. Anyways thanks to prompt response

Sanjay Radadiya Over a year ago

any error occured ? first u need to run paste.ubuntu.com/21378705 and after above query is usefull.

Collectives™ on Stack Overflow

How to transpose column into row in oracle sql 11G

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related