Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

SQL Query

597023
597023 Member Posts: 11
edited September 2007 in SQL & PL/SQL
Hi,

I have a table with a single column of number type.
and the data is as follows

col1
----------
12
22
33
-4
-6
-4

Now i need a query which gives the output as follows
(Two columns positive and negative)

positive negative
---------- ------------
12 -4
22 -6
33 -4

Comments

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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.
  • 105967
    105967 Member Posts: 1,027
    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)
  • 597023
    597023 Member Posts: 11
    Thanks to rob & leo
This discussion has been closed.