Query to output horizontal output

Question

I need to display the query output in a horizontal way. I have sample data

create table TestTable (id number, name varchar2(10)) insert into TestTable values (1, 'John') insert into TestTable values (2, 'Mckensy') insert into TestTable values (3, 'Valneech') insert into TestTable values (4, 'Zeebra') commit select * from TestTable 

Gets output in a vertical view.

 ID Name ========== 1 John 2 Mckensy 3 Valneech 4 Zeebra 

However, I need to display it horizontally.

 ID 1 2 3 4 Name John Mckensy Valneech Zeebra 

How can I do that?

Answer 1

To rotate, you must use the pivot clause of the select statement:

 select * from testtable pivot ( max(name) for id in (1,2,3,4) ) 

This is not particularly pleasant to do in SQL, so you should carefully consider whether this is really what you want to do. I usually use Oracle Base to rotate examples, but there are many.

Here's a little SQL Fiddle to demonstrate.

Answer 2

Perhaps this will help you:

 select 'id', LISTAGG(id, ' ') WITHIN GROUP (ORDER BY name) from testtable union select 'name', LISTAGG(name, ' ') WITHIN GROUP (ORDER BY name) from testtable 

EDIT:

or with an axis:

 create table TestTable2 (id varchar2(30), name varchar2(10)); insert into TestTable2 values ('id', 'name'); insert into TestTable2 select cast(id as varchar2(30)) as id , name from testtable select * from testtable2 pivot ( max(name) for id in ('id',1,2,3,4) ) 

Answer 3

PIVOT operator is what you are looking for.