How to remove NULL values from two rows in a table

Question

output I am getting is this.

2015-10-01 NULL
NULL NULL
NULL NULL
NULL 2015-10-05
2015-10-11 NULL
NULL 2015-10-13
2015-10-15 2015-10-16
2015-10-25 NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL 2015-10-31

I want this to be

2015-10-01  2015-10-05
2015-10-11  2015-10-13
2015-10-15  2015-10-16
2015-10-25  2015-10-31

My code:

select (case when (end_lag <> start_date) or end_lag is null then start_date end) as start_date, 
       (case when (start_lead <> end_date) or start_lead is null then end_date end) as end_date
from
    (select lead(start_date) over(order by start_date) as start_lead, start_date, end_date,                     lag(end_date) over(order by end_date) as end_lag
    from projects) t1;

original table has two attributes (start_date, end_date), I have created the lead column for start_date and lag column for end_date

Does this answer your question? [How to remove 'NULL' from results of queries SQL Server 2008](https://stackoverflow.com/questions/34215239/how-to-remove-null-from-results-of-queries-sql-server-2008) — , Sep 05 '21 at 16:46

score 1 · Answer 1 · answered Sep 05 '21 at 18:13

From current results table would go with:

select start_date, end_date
from (select row_number() over(order by null) rn, start_date
      from current_t
      where start_date is not null) a
join (select row_number() over(order by null) rn, end_date
      from current_t
      where end_date is not null) b
on b.rn = a.rn;

(sql fiddle here)

Serg · Answer 2 · 2021-09-05T17:23:11.360

Ignore two NULLs and take lead value from your original query. I guess it could be simplified, hard to know without DDL and sample data.

select *
from (
   select start_date, 
          case when end_date is null then lead(end_date) over(order by coalesce(start_date, end_date)) else end_date end end_date
   from (
      select * 
      from (
           -- your original query
           select (case when (end_lag <> start_date) or end_lag is null then start_date end) as start_date, 
                 (case when (start_lead <> end_date) or start_lead is null then end_date end) as end_date
           from (
             select lead(start_date) over(order by start_date) as start_lead, start_date, end_date,                     
             lag(end_date) over(order by end_date) as end_lag
             from projects) t1
          ---
     ) tbl
     where not (start_date is null and end_date is null )
   ) t
) t
where start_date is not null
order by start_date;

score 0 · Answer 3 · answered Sep 05 '21 at 17:23

You don't seem to have an ordering for your rows. So, you can just unpivot and pair them up:

select min(dte), nullif(max(dte), min(dte))
from (select x.dte, row_number() over (order by dte) as seqnum
      from projects p cross join lateral
           (select p.start_date as dte from dual union all
            select p.end_date from dual
           ) x
     ) p
group by ceil(seqnum / 2)

How to remove NULL values from two rows in a table

3 Answers3