display table row into column

[shweta....@googlemail.com]

Hi All

I have :

SQL> create table t1( col1 varchar2(10), col2 varchar2(10),col3
varchar2(10));

Table created.

SQL>

SQL> insert into t1(col1,col2,col3) values ('A','B','C');

1 row created.

SQL> insert into t1(col1,col2,col3) values ('D','E','F');

1 row created.

SQL> insert into t1(col1,col2,col3) values ('G','H','I');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

COL1 COL2 COL3
---------- ---------- ----------
A B C
D E F
G H I

i want to write select query which should give me the output:
as

col1 col2 col3
---- ------ ------
B E H

Regards

Shweta

[johnb...@sbcglobal.net]

Did you look at Tom Kyte's site asktom.oracle.com?

Do a little searching around there lots of examples already out there.

[gar...@jamms.org]

This is a very popular question. Search the group, or asktom, for
"pivot query".

HTH

-g

[shweta....@googlemail.com]

> -g- Hide quoted text -
>
> - Show quoted text -

Thanks all.

[sriniva...@gmail.com]

SELECT (SELECT col2 FROM t1 WHERE col1='A') col1,(SELECT col2 FROM
t1 WHERE col1='D') col2,(SELECT col2 FROM t1 WHERE col1='G') col3 FROM
t1
WHERE ROWNUM<2

[Shakespeare]

sriniva...@gmail.com schreef:

Duh.... why not:
select 'B' col1, 'E' col2, 'H' col3 from dual?
If you want to hard-code your queries, be consequent!

Shakespeare

[Michael Austin]

Srini - say WHAT??? you might as well have hard-code the results too...

I see nothing in his data that suggests there is relationship between
the 3 rows he is suggesting.. fix the database design before trying to
solve a problem that shouldn't exist.