Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert columns to rows.

Posted on 2004-08-20
9
Medium Priority
?
4,840 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 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

661 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