Q

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.

This was first published in January 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close