CODEWITHSUNDEEP
sqloracle-database
Kont Dooku
Kont Dooku

Reputation: 58

Get Distinct values without null

I have a table like this;

--Table_Name--
A   |  B    | C
-----------------
A1    NULL    NULL   
A1    NULL    NULL    
A2    NULL    NULL    
NULL  B1      NULL    
NULL  B2      NULL    
NULL  B3      NULL    
NULL  NULL    C1

I want to get like this ;

--Table_Name--
A   |  B    | C
-----------------
A1    B1      C1    
A2    B2      NULL    
NULL  B3      NULL

How should I do that ?

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you have only one value per column, then I think a simpler solution is to enumerate the values and aggregate:

select max(a) as a, max(b) as b, max(c) as c
from (select t.*,
             dense_rank() over (partition by (case when a is null then 1 else 2 end),
                                             (case when b is null then 1 else 2 end),
                                             (case when c is null then 1 else 2 end)
                                order by a, b, c
                               ) as seqnum                                                 
      from t
     ) t
group by seqnum;

This only "aggregates" once and only uses one window function, so I think it should have better performance than handling each column individually.

Another approach is to use lateral joins which are available in Oracle 12C -- but this assumes that the types are compatible:

select max(case when which = 'a' then val end) as a,
       max(case when which = 'b' then val end) as b,
       max(case when which = 'c' then val end) as c      
from (select which, val,
             dense_rank() over (partition by which order by val) as seqnum
      from t cross join lateral
           (select 'a' as which, a as val from dual union all
            select 'b', b from dual union all
            select 'c', c from dual
           ) x
      where val is not null
     ) t
group by seqnum;

The performance may be comparable, because the subquery removes so many rows.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

Here's one option:

  • sample data is from line #1 - 9
  • the following CTEs (lines #11 - 13) fetch ranked distinct not null values from each column
  • the final query (line #15 onward) returns desired result by outer joining previous CTEs on ranked value
SQL> with test (a, b, c) as
  2  (select 'A1', null, null from dual union all
  3   select 'A1', null, null from dual union all
  4   select 'A2', null, null from dual union all
  5   select null, 'B1', null from dual union all
  6   select null, 'B2', null from dual union all
  7   select null, 'B3', null from dual union all
  8   select null, null, 'C1' from dual
  9  ),
 10  --
 11  ta as (select distinct a, dense_rank() over (order by a) rn from test where a is not null),
 12  tb as (select distinct b, dense_rank() over (order by b) rn from test where b is not null),
 13  tc as (select distinct c, dense_rank() over (order by c) rn from test where c is not null)
 14  --
 15  select ta.a, tb.b, tc.c
 16  from ta full outer join tb on ta.rn = tb.rn
 17          full outer join tc on ta.rn = tc.rn
 18  order by a, b, c
 19  /

A  B  C
-- -- --
A1 B1 C1
A2 B2
   B3

SQL>

Upvotes: 2

Related Questions