Solved

Convert columns to rows.

Posted on 2004-08-20
9
4,812 Views
Last Modified: 2008-01-09
I have 2 tables in Oracle:

1) DATA_TABLE
2) DETAIL_TABLE

The columns of DATA_TABLE are as follows:
DT1       
DT2       
DT3       
DT4

The columns of DETAIL_TABLE are as follows:
DT_VALUE

Now suppose the data in DATA_TABLE is as follows:
DT1       DT2       DT3       DT4
1      2      3      4

How do i retrieve and insert the above in DETAIL_TABLE as follows?:
DT_VALUE
1
2
3
4


One way of solving this is to loop through the columns in DATA_TABLE and in each loop pick up the value of the column and insert the same to the DETAIL_TABLE.
But this would mean, keeping the above exmaple in mind, that the DATA_TABLE will be queried 4 times and it will involve 4 inserts in DETAIL_TABLE.

Can't this be done using 1 query and 1 insert?

TIA,
Dhanesh.
0
Comment
Question by:dhaneshgesota
  • 5
  • 2
  • 2
9 Comments
 
LVL 6

Expert Comment

by:morphman
ID: 11857959
Yes.

Multitable insert using the pivoting technique.

INSERT ALL
  INTO DETAIL_TABLE VALUES (1,DT1)
  INTO DETAIL_TABLE VALUES (1,DT2)
  INTO DETAIL_TABLE VALUES (1,DT3)
  INTO DETAIL_TABLE VALUES (1,DT4)
SELECT DT1,DT2,DT3,DT4 from DATA_TABLE;

Cheers
Dan
0
 
LVL 6

Accepted Solution

by:
morphman earned 250 total points
ID: 11857960
Sorry that would be

INSERT ALL
  INTO DETAIL_TABLE VALUES (1,DT1)
  INTO DETAIL_TABLE VALUES (2,DT2)
  INTO DETAIL_TABLE VALUES (3,DT3)
  INTO DETAIL_TABLE VALUES (4,DT4)
SELECT DT1,DT2,DT3,DT4 from DATA_TABLE;
0
 

Author Comment

by:dhaneshgesota
ID: 11858057
Thanks morphman! That was what i wanted.
One more question befor i close this: Is there anything similar to INSERT ALL while updating???

TIA,
Dhanesh.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Expert Comment

by:morphman
ID: 11858714
Dont think so. You would have to perform plsql to do it.

You might be better off perform a multitable insert into a temp space, and then merging the results with your original table.
0
 
LVL 6

Expert Comment

by:morphman
ID: 11858722
It would still be faster than 4 table scans of DATA_TABLE, as it would be one scan for the multitable insert, and a single join back to the original table.
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 11867338
hi

i think u can use union also to solve ur problem.

insert into detail_table(dt_value)
values(select dt1 from data_table
          union all
          select dt2 from data_table
          union all
         select dt3 from data_table
          union all
         select dt4 from data_table);

update detail_table a
set dt_value = (select dt1 from data_table b
                      where a.dt_value = b.dt1
          union all
          select dt2 from data_table c
          where a.dt_value = c.dt2
          union all
         select dt3 from data_table d
         where a.dt_value = d.dt3
          union all
         select dt4 from data_table e
         where a.dt_value = e.dt3);

i hope the above solves ur problem.

regards
annamalai
0
 
LVL 6

Expert Comment

by:morphman
ID: 11867708
The union causes multiple table scans of the source table.

Insert ALL was introduced at 9i to specifically address the problem of data pivoting with a single pass through of each table.

UNION was the only way pre 9i to do table pivoting in standard sql.
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 11867733
hi

i just gave him another option. and i didnt know the oracle version our friend is using.

regards
annamalai
0
 

Author Comment

by:dhaneshgesota
ID: 11890404
Thanks morphman for your answer!
annamalai77, I was aware of the joins but didnt want to do it as it would lead to multiple table scans. That would have been my final choice if it wouldnt have been for the INSERT ALL.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

813 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now