?
Solved

How to convert multiple columns to rows in Oracle

Posted on 2009-04-08
5
Medium Priority
?
7,405 Views
Last Modified: 2013-12-19
I have a table that look like this

COL1      COL2      COL3

10      20      30
100      200      300

I want to reverse the colomns and rows, i.e., producing the following output
COL1      10      100
COL2      20      200
COL3      30      300

As there is no PIVOT function in oracle 91, i had to use DECODE
 SELECT DECODE(rn,1,'COL1',
                  2,'COL2',
                     3,'COL3') col_type,
         DECODE(rn,1,col1,
                    2,col2,
                     3,col3) VALUE
    FROM   t,
           (SELECT LEVEL rn
            FROM   dual
           CONNECT BY LEVEL <= 3) ;

It produces the following result
COL_TYPE      VALUE

COL1      10
COL1      100
COL2      20
COL2      200
COL3      30
COL3      300

How can I turn each column in the original table into each corresponding row and achieve the output as I mentioned in the beginning?

create table t (col1 integer, col2 integer, col3 integer);
insert into t values (10,20,30);
insert into t values (100,200,300);
select * from t;

Open in new window

0
Comment
Question by:suecnus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24103297
you can generate the columns dynamically by joining to user_tab_cols
SELECT   column_name, DECODE(n,
                             1,
                             col1,
                             2,
                             col2,
                             3,
                             col3)
                          VALUE
  FROM   t, (    SELECT   LEVEL n
                   FROM   DUAL
             CONNECT BY   LEVEL <= 3), user_tab_cols utc
 WHERE   table_name = 'T' AND column_id = n

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24103303
or you can just hardcode them, which you might as well do anyway since you have to hardcode them as part of the decode...
SELECT   DECODE(n,
                1,
                'COL1',
                2,
                'COL2',
                3,
                'COL3')
             col_type, DECODE(n,
                              1,
                              col1,
                              2,
                              col2,
                              3,
                              col3)
                           VALUE
  FROM   t, (    SELECT   LEVEL n
                   FROM   DUAL
             CONNECT BY   LEVEL <= 3)

Open in new window

0
 

Author Comment

by:suecnus
ID: 24103728
Thanks for the answer, but isn't your Solution 2 (hardcoded columns) the same as the one I used in my question? It does not produce the desired output.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24103829
they both produce the same output, and that's because I was looking at your question wrong,  I was trying to duplicate the last results you posted (what you didn't want),
not the earlier ones which are what you wanted

sorry about that, that was my confusion
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24103879
try this...

if you have more than 2 rows then simply add more of the MAX(DECODE....)) columns for each row you'll need to pivot.
You will need to know how many rows before hand though since SQL can't have dynamic columns.

If you need that you'll have to use pl/sql to generate a sql statement on the fly.
  SELECT   column_name col_type,
           MAX(DECODE(rn, 1, DECODE(n,
                                    1,col1,
                                    2,col2,
                                    3,col3))) x,
           MAX(DECODE(rn, 2, DECODE(n,
                                    1,col1,
                                    2,col2,
                                    3,col3))) y
    FROM   (  SELECT   col1, col2, col3, ROW_NUMBER() OVER (ORDER BY col1) rn FROM t),
           (    SELECT   LEVEL n
                  FROM   DUAL
            CONNECT BY   LEVEL <= 3),
           user_tab_cols utc
   WHERE   table_name = 'T' AND column_id = n
GROUP BY   column_name

Open in new window

0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question