Forum Stats

  • 3,728,544 Users
  • 2,245,649 Discussions
  • 7,853,590 Comments

Discussions

Ignore NULLS on 2 different columns

Kannan Sekar
Kannan Sekar Member Posts: 51 Blue Ribbon

I have table like below

The required output is;

COL1 COL2

------------------

AAA DDD

BBB EEE

CCC FFF

Tagged:

Answers

  • mathguy
    mathguy Member Posts: 9,730 Gold Crown

    What tells you which values should be paired together? For example, why does AAA go with DDD, and not with FFF? Or is it random (any pairing is acceptable)?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond

    Hi, @Kannan Sekar

    Do you want to show the n-th value of col1 (in alphabetic order) and the n-th value of col2 (in alphabetic order) together on the same output row? Here's one way:

    WITH  got_rns  AS
    (
      SELECT col1, col2
      ,      ROW_NUMBER () OVER (ORDER BY col1) AS rn1
      ,      ROW_NUMBER () OVER (ORDER BY col2) AS rn2
      FROM   table_x
    )
    SELECT   c1.col1
    ,	 c2.col2
    FROM	 got_rns c1
    JOIN	 got_rns c2 ON c1.rn1 = c2.rn2
    WHERE	 c1.col1  IS NOT NULL
    OR	 c2.col2  IS NOT NULL
    ORDER BY c1.col1, c2.col2 -- or whatever you want
    ;
    

    depending on your exact requirements and your data.

    If you'd care to post CREATE TABLE and INSERT statements for your data, then I could test it.

    In every row of your table, will exactly one of the columns be NULL? Will you always have exactly the same number of non-NULL col1s as non-NULL col2s? Depending on your answers there could be more efficient ways to do it.

    The solution above will still work if col1 and col2 have different data types.

  • Kannan Sekar
    Kannan Sekar Member Posts: 51 Blue Ribbon

    @Frank Kulash

    No, we will get different number of non-NULL col1s and col2s..

  • User_H3J7U
    User_H3J7U Member Posts: 88 Blue Ribbon
    with t (a, b) as (
    select 11,  null from dual union all
    select null, 22  from dual union all
    select null, null from dual union all
    select 14,  24 from dual union all
    select null, 25  from dual)
    select a, b
    from (
     select
       nth_value(a,rownum) ignore nulls over(order by a rows between unbounded preceding and unbounded following) a,
       nth_value(b,rownum) ignore nulls over(order by b rows between unbounded preceding and unbounded following) b
     from t)
    where a is not null or b is not null
    order by a,b;
    
            A         B
    ---------- ----------
           11        22
           14        24
                     25
    


    Christian Osvaldo González
  • Christian Osvaldo González
    Christian Osvaldo González Member Posts: 16 Red Ribbon
Sign In or Register to comment.