SQL & PL/SQL

SQL> create table mytable
  2  as
  3  select 12 col1 from dual union all
  4  select 22 from dual union all
  5  select 33 from dual union all
  6  select -4 from dual union all
  7  select -6 from dual union all
  8  select -4 from dual union all
  9  select -8 from dual union all
 10  select -9 from dual
 11  /

Tabel is aangemaakt.

SQL> select case when max(col1) > 0 then max(col1) end positive
  2       , case when min(col1) < 0 then min(col1) end negative
  3    from ( select col1
  4                , row_number() over (partition by sign(col1) order by null) rn
  5             from mytable
  6         )
  7   group by rn
  8  /

                              POSITIVE                               NEGATIVE
-------------------------------------- --------------------------------------
                                    12                                     -4
                                    22                                     -6
                                    33                                     -8
                                                                           -4
                                                                           -9

5 rijen zijn geselecteerd.

Note1: the pairing of the columns is completely arbitrary.
Note2: all zero values will be discarded

Regards,
Rob.

This is a bit strange as there is no ordering key but here it goes:

SQL> get tt
  1  select v1.c1 col1, v2.c1 col2
  2  from   (   ( select c1, rownum rn from t1 where c1 >= 0 ) v1 full outer join
  3             ( select c1, rownum rn from t1 where c1 <  0 ) v2
  4             on  v1.rn = v2.rn
  5*    )
SQL> /
 
      COL1       COL2
---------- ----------
        12         -4
        22         -6
        33         -4
 
SQL> 

The full outer join is there to get all records if there more numbers > 0 than number < 0.
(I have included the 0 into the numbers > 0)