## Forum Stats

• 3,769,020 Users
• 2,252,898 Discussions

Discussions

# Ignore NULLS on 2 different columns

Member Posts: 51 Blue Ribbon

I have table like below

The required output is;

COL1 COL2

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

AAA DDD

BBB EEE

CCC FFF

Tagged:

• Member Posts: 10,155 Blue Diamond

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)?

• Member, Moderator Posts: 41,218 Red Diamond

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
;
```

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.

• Member Posts: 51 Blue Ribbon

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

• Member Posts: 666 Silver Trophy
```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
```

• Member Posts: 19 Red Ribbon

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