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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.