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 5From 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 xHow 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available here.
- 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 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.