Changing the physical orientation of a table using SQL

I have data in a table in following way:

Dept
====
Deptno         Dname
10             aaa
20             bbb
30             ccc
40             ddd

With the help of an SQL query, I need the result in the following way:

Depno     10     20      30    40
Dname     aaa    bbb    ccc    ddd

I am using ORALCE 8.1.7 on Sun 2.0.8

The following script will pivot table t as you requested:

Create table t (Deptno  integer,
                Dname char(5));
insert into t values (10,'aaa');
insert into t values (20,'bbb');
insert into t values (30,'ccc');
insert into t values (40,'ddd');


select max (decode (Deptno,10,to_char(Deptno),null)) as f1,
       max (decode (Deptno,20,to_char(Deptno),null)) as f2,
       max (decode (Deptno,30,to_char(Deptno),null)) as f3,
       max (decode (Deptno,40,to_char(Deptno),null)) as f4
from t
union all
select max (decode (Deptno,10,Dname,null)) as f1,
       max (decode (Deptno,20,Dname,null)) as f2,
       max (decode (Deptno,30,Dname,null)) as f3,
       max (decode (Deptno,40,Dname,null)) as f4
from t;

For More Information

• What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.