Home » SQL & PL/SQL » SQL & PL/SQL » How can I bifurcate result set into 2?
How can I bifurcate result set into 2? [message #248466] Fri, 29 June 2007 02:26 Go to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
I have the following table data:

COL1 COL2 COL3 COL4
---- ---- ---- ----
1 A AA AAA
2 B BB BBB
3 C CC CCC
4 D DD DDD
5 E EE EEE

I want an optimized SQL or PL/SQL to generate the following output.
Means all odd rows on left-hand side and even rows on right-hand side.

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
---- ---- ---- ---- ---- ---- ---- ----
1 A AA AAA 2 B BB BBB
3 C CC CCC 4 D DD DDD
5 E EE EEE
Re: How can I bifurcate result set into 2? [message #248471 is a reply to message #248466] Fri, 29 June 2007 02:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
This is the first thing I came up with:
SQL> WITH yourtable AS
  2  (
  3    SELECT 1 col1, 'A' col2, 'AA' col3, 'AAA' col4 FROM dual UNION ALL
  4    SELECT 2 col1, 'B' col2, 'BB' col3, 'BBB' col4 FROM dual UNION ALL
  5    SELECT 3 col1, 'C' col2, 'CC' col3, 'CCC' col4 FROM dual UNION ALL
  6    SELECT 4 col1, 'D' col2, 'DD' col3, 'DDD' col4 FROM dual UNION ALL
  7    SELECT 5 col1, 'E' col2, 'EE' col3, 'EEE' col4 FROM dual
  8  )
  9  SELECT MAX(DECODE(MOD(rn,2), 1, col1, NULL)) col1
 10       , MAX(DECODE(MOD(rn,2), 1, col2, NULL)) col2
 11       , MAX(DECODE(MOD(rn,2), 1, col3, NULL)) col3
 12       , MAX(DECODE(MOD(rn,2), 1, col4, NULL)) col4
 13       , MAX(DECODE(MOD(rn,2), 0, col1, NULL)) col5
 14       , MAX(DECODE(MOD(rn,2), 0, col2, NULL)) col6
 15       , MAX(DECODE(MOD(rn,2), 0, col3, NULL)) col7
 16       , MAX(DECODE(MOD(rn,2), 0, col4, NULL)) col8
 17  FROM   ( SELECT col1
 18                , col2
 19                , col3
 20                , col4
 21                , row_number() OVER ( ORDER BY col1 ) rn
 22            FROM yourtable
 23         )
 24  GROUP  BY FLOOR(rn/2)
 25  /

      COL1 C CO COL       COL5 C CO COL
---------- - -- --- ---------- - -- ---
         3 C CC CCC          2 B BB BBB
         5 E EE EEE          4 D DD DDD
         1 A AA AAA

SQL>


MHE
Re: How can I bifurcate result set into 2? [message #248472 is a reply to message #248466] Fri, 29 June 2007 02:43 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select 
  max(decode(mod(col1-1,2),0,col1)) col1,
  max(decode(mod(col1-1,2),0,col2)) col2,
  max(decode(mod(col1-1,2),0,col3)) col3,
  max(decode(mod(col1-1,2),0,col4)) col4,
  max(decode(mod(col1-1,2),1,col1)) col5,
  max(decode(mod(col1-1,2),1,col2)) col6,
  max(decode(mod(col1-1,2),1,col3)) col7,
  max(decode(mod(col1-1,2),1,col4)) col8
from mytable
group by trunc(col1-1/2)
/

Regards
Michel

Previous Topic: Using % operator
Next Topic: How to spool output of PL/SQL procedure/function to file?
Goto Forum:
  


Current Time: Sat Dec 10 22:30:49 CST 2016

Total time taken to generate the page: 0.13962 seconds