Solved

converting cols to rows in oracle

Posted on 2011-03-03
18
592 Views
Last Modified: 2013-12-19
I have a row

col1,col2,col3,col4

would like to display it  like below ( basically converting col3 col4 into rows under col1 and col2)

col1,col2
col3,col4
0
Comment
Question by:aboj
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35029151
How much data?

If not much:
select col1,col2 from table
union all
select col3, col4 form table;

If you have a lot of data or more complex requirements we can tweak this of offer up better solutions.
0
 

Author Comment

by:aboj
ID: 35029160

I dont want to use union all any other method as it causes performance issues
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35029169
If you want to keep the columns together with their row and have a unique id, something like:

select col1, col2
from
(
select 1 sort_order, id, col1,col2 from table
union all
select 2 sort_order, id, col3,col3 from table
)
order by id,sort_order
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35029189
What is the final end destination of the data?

Do you physically need two rows or can you 'simulate' two rows with a CR/LF character in an output file?

There are likely some XML tricks we can do but I would like to know more about how the results will be used.
0
 

Author Comment

by:aboj
ID: 35029191
'union all' or 'union' does not work for me , please any other approach is appricated
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35029209
>>'union all' or 'union' does not work for me

I was typing my second response when you posted.  I did not see that until I had already posted mine.
0
 

Author Comment

by:aboj
ID: 35029214

this should be in select sql statement of oracle
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35029230
>>this should be in select sql statement of oracle

That doesn't tell me what this select statement will be feeding.  What are you doing with the results of the select statement?

Just spooling it out to a flat file, calling a stored procedure using a ref_cursor, returning the results to Java/.Net or some other code?
0
 

Author Comment

by:aboj
ID: 35029249

creating a view for with the result
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 73

Expert Comment

by:sdstuber
ID: 35029599

try this,  the union is not on your table

select decode(n,1,col1,2,col3),  decode(n,2,col2,col4) from
yourtable, (select 1 n from dual union all select 2 n from dual)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35029605
note the union in my query is NOT on your table, it's on dual.  So the performance cost is a few milliseconds.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35030110
I was working on this so I figured I would post it.

It may or may not out-perform the sql in http:#a35029599.
You'll need to test in your environment.
--set up some dummy data
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1), col3 char(1), col4 char(1));
insert into tab1 values('a','b','c','d');
commit;

begin
	for i in 1..15 loop
		insert into tab1 (select * from tab1);
	end loop;
end;
/
commit;


--the SQL
with myTab as (select
	xmlelement("a",
	xmlagg(
	xmlforest(
		xmlforest(col1, col2) as "b",
		xmlforest(col3 as col1, col4 as col2) as "b"
	)
	)
	) myXML
from tab1
)
select
	extractvalue(column_value,'/b/COL1/text()') first,
	extractvalue(column_value,'/b/COL2/text()') second
from
(
	select column_value from myTab,
	table(xmlsequence(extract(myTab.myXML ,'/a/b')))
)
;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35030296
In my testing with approximately 80000 rows I got 0.611 seconds with the join to 2 row dual view  and 82 seconds with the xml version.

the join version used 2000 gets (consistent and current)  xml used 10000000
all other measures for the xml version were larger too, it just can't scale as well as the join
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35030362
In further testing I screwed something up.  Please ignore my code in http:#a35030110
0
 
LVL 1

Accepted Solution

by:
ocolpas earned 500 total points
ID: 35170132
try the following

SELECT   DECODE (MOD (cj.key, 2), 1, 'col1', 0, 'col3'),
         DECODE (MOD (cj.key, 2), 1, 'col2', 0, 'col4')
    FROM (SELECT ROWNUM AS KEY
            FROM dba_objects
           WHERE ROWNUM < 3) cj,
           source_table
ORDER BY keys_that_uniquely_ids_source_row, cj.KEY

this creates twice the number of rows from the one table you are using by forcing a cross join. you then just pick the columns you want base on whether the row in odd or even.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35170267
ocolpas - that's essentially the same code posted above except you've included a less efficient query against dba_objects than the UNION of DUAL queries, plus dba_objects requires heightened privileges
0
 
LVL 1

Expert Comment

by:ocolpas
ID: 35178288
sdstuber - you're right, I saw the better code after I hit the submit button... first time/noob goof.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35999107
aboj,

the accepted answer is a worse version than what was posted previously (http:#35029599 ), the author of that post even agrees it's not as good.

Please explain why you chose that answer as the accepted solution

plus, even if you don't agree to change the accepted answer, I highly recommend NOT using that answer as it is less efficient and will simply fail in some instances
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 …
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

867 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