Convert result from one column to 2 rows and more

Question

Is it possible to take from one column the result and transfer it to two columns. By taking the first and second results as start and end and everyone else in the same way. i used pivot but didn't get any real result.

current result

 rn old_date_row 

 1   01-JUN-18
 2   null 
 3   null 
 4   null
 5   22-JUN-18
 6   null
 7   null
 8   null
 9   25-JUN-19
 10  null
 11  null
 12  25-JUN-20

expected result new table

    start_date      end_date
   01-JUN-18       22-JUN-18    
   25-JUN-19       25-JUN-20

Answer 1

You need to enumerate rows that are not null using row_number() then identify successive rows using round(rid/2) :

select old_date_row, rid, round(rid/2)
from (
  select old_date_row, row_number() over (order by rn) rid
  from mytable t
  where old_date_row is not null
)

Results :

OLD_DATE_ROW    RID ROUND(RID/2)
01-06-18        1   1
22-06-18        2   1
25-06-19        3   2
25-06-20        4   2

The final query can be :

select min(old_date_row) as start_date, max(old_date_row) as end_date
from (
  select old_date_row, row_number() over (order by rn) rid
  from mytable t
  where old_date_row is not null
)
group by round(rid/2);

Results :

START_DATE  END_DATE
01-06-18    22-06-18
25-06-19    25-06-20

Demo here