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, Sep 08 '12 at 07: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, Sep 08 '12 at 07:26

score 3 · Answer 1 · answered Sep 08 '12 at 07:40

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 08 '12 at 07:40

Ben

51,770
36
127
149

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? – AWrightIV Apr 25 '16 at 23:57
[SQL Fiddle generally no longer works](http://meta.stackoverflow.com/q/321437/458741) @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. – Ben Apr 26 '16 at 06:09

Robert · Answer 2 · 2012-09-08T09:54:52.810

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 08 '12 at 09:54

answered Sep 08 '12 at 07:36

Robert

25,425
8
67
81

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 – user1656315 Sep 08 '12 at 08:21

score 0 · Answer 3 · answered Sep 08 '12 at 07:36

0

PIVOT operator is what you are looking for.

answered Sep 08 '12 at 07:36

Michal Klouda

14,263
7
53
77

Query to display output horizontally

3 Answers3

Linked