Solved

Convert columns to rows.

Posted on 2004-08-20
9
4,802 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
 
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
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: 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

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.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
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…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

744 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

12 Experts available now in Live!

Get 1:1 Help Now