?
Solved

Convert columns to rows.

Posted on 2004-08-20
9
Medium Priority
?
4,853 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 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month12 days, 23 hours left to enroll

579 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