Q
Problem solve Get help with specific problems with your technologies, process and projects.

Converting values of one column as a separate column

I have a table that looks like this:

 Table Name : Test
 columns:
 col1 varchar2(10), 
 col2 varchar2(10),
 col3 number(3)
Data avaliable in the table:
 
 col1   col2     col3
 ---    ----     ----
 
 1      va11     1
 1      val2     2
 1      val3     3
 1      val4     4
 2      val1     6
 2      val5     5
 
From this table, I want a report that looks like:
 
 col1 val1 val2  val3  val4 val5
 ---  ---  ----  ----  ---- ----
 1     1     2     3    4     x
 2     6     x     x    x     x
How can I convert values of one column as a separate column?


In Oracle, you can use the DECODE function to turn a table "on its side". For instance, try the following query:

SELECT col1,SUM(DECODE(col2,'val1',col3,0)) AS val1,
SUM(DECODE(col2,'val2',col3,0)) AS val2,
SUM(DECODE(col2,'val3',col3,0)) AS val3,
SUM(DECODE(col2,'val4',col3,0)) AS val4,
SUM(DECODE(col2,'val5',col3,0)) AS val5,
SUM(DECODE(col2,'val6',col3,0)) AS val6,
FROM test
GROUP BY col1;

For More Information


This was last published in June 2002

Dig Deeper on Oracle database design and architecture

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close