Ignore NULLS on 2 different columns

Kannan Sekar

I have table like below

The required output is;

COL1 COL2

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

AAA DDD

BBB EEE

CCC FFF

mathguy

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

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

@Frank Kulash

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

User_H3J7U

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

https://community.oracle.com/tech/developers/discussion/comment/16791650#Comment_16791650

Amazing, thank for teachme that, I did not know that this was possible