Solved

converting cols to rows in oracle

Posted on 2011-03-03
18
586 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

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Syntax 8 58
oracle query 15 63
select query - oracle 16 82
Outer Query not returning data - SQL HELP 16 41
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

759 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

23 Experts available now in Live!

Get 1:1 Help Now