Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

converting cols to rows in oracle

Posted on 2011-03-03
18
Medium Priority
?
601 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 78

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 78

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 78

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 78

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 78

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
 
LVL 74

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 74

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 78

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 74

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 78

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 2000 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 74

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 74

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

885 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