PL/SQL Select values in rows (vertical)

Question

I'm trying to select values from my DB. Right now I get the result set horizontal displayed but I want it in a vertical way.

SELECT * FROM myTable;

With this Query the result Looks like this:

ID | Name | Age | City_id
1    A       20    2

But I want it looking like this:

1
A
20
2

Is there a solution for my Problem.

I tryied to use UNPIVOT but didn't work

Answer 1

Xml and xquery .

select * from xmltable('for $i in ./ROWSET/ROW/* return $i' 
passing xmltype(dbms_xmlgen.getxml('select * from all_objects where rownum < 2'))
     columns colum_name varchar2(100) path 'local-name()'
             ,         text varchar2(100) path 'text()'
     );

In your case, you can use it like below

select text from
(     
select * from 
xmltable('for $i in ./ROWSET/ROW/* return $i' 
passing xmltype(dbms_xmlgen.getxml('select * from myTable where rownum < 2'))
     columns colum_name varchar2(100) path 'local-name()'
             ,text varchar2(100) path 'text()'
             )
 )