Query to display output horizontally

Question

I need to display a query output in a horizontal manner. I have some example 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

This gets the 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 one do this?

this is more of a display format issue, why would you like the data to come out of the DB like that, surely it would be easier to do this in the front end via some reporting service. — Qpirate
– Qpirate, Commented Sep 8, 2012 at 7:23
Hi Warrior, Thanks. for my requirement backend data is very less. we are not using any report tool. If i get the data through query, my job is completed 100% . So I am in need of that query. Thanks. — user1656315
– user1656315, Commented Sep 8, 2012 at 7:26

Ben · Accepted Answer · 2012-09-08 07:40:52Z

3

To pivot, you should 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 pretty to do in SQL, so you should consider carefully whether this is what you want to do. I normally use Oracle Base for pivoting examples but there are many out there.

Here's a little SQL Fiddle to demonstrate.

answered Sep 8, 2012 at 7:40

Ben

53.1k36 gold badges133 silver badges156 bronze badges

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

2 Comments

allanwright Over a year ago

The SQL Fiddle link no longer works, though there's no reason for anyone to be surprised based on when this answer was posted. Could you update it?

Ben Over a year ago

SQL Fiddle generally no longer works @AWright, it's not specific to this answer. I can't get the thing to load, but rereading this answer it'll be some sample data and a simple pivot statement. More of a "try it yourself" than any new information. The OP has already provided the DDL and DML for a small example, so it should be simple to run it yourself.

Robert · Accepted Answer · 2012-09-08 09:54:52Z

1

Maybe it 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 pivot:

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) 
)

edited Sep 8, 2012 at 9:54

answered Sep 8, 2012 at 7:36

Robert

25.8k8 gold badges70 silver badges86 bronze badges

1 Comment

user1656315 Over a year ago

Hi, Thanks. Your reply is very close to my requirement. But output should be in the columns. not just space between columns. Little more help is appreciated. Thanks

Michal Klouda · Accepted Answer · 2012-09-08 07:36:08Z

-1

PIVOT operator is what you are looking for.

answered Sep 8, 2012 at 7:36

Michal Klouda

14.6k7 gold badges58 silver badges79 bronze badges

Collectives™ on Stack Overflow

Query to display output horizontally

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related