Solved

Convert columns to rows.

Posted on 2004-08-20
9
4,834 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
[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
  • 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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

622 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