Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t ( s_no, id, start_date, end_date ) AS
SELECT 1, 1, DATE '2020-10-11', DATE '2020-10-11' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-11-04', DATE '2020-12-11' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2020-11-05', DATE '2020-11-10' FROM DUAL UNION ALL
SELECT 4, 1, DATE '2020-11-06', DATE '2020-11-10' FROM DUAL UNION ALL
SELECT 5, 1, DATE '2020-11-20', DATE '2020-12-20' FROM DUAL UNION ALL
SELECT 6, 1, DATE '2021-01-01', DATE '2021-01-20' FROM DUAL UNION ALL
SELECT 7, 1, DATE '2021-01-01', DATE '2021-03-25' FROM DUAL;
7 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select id, min(start_date), max(end_date),
       listagg(s_no, ',') within group (order by s_no) as s_nos
from (select t.*,
             sum(case when prev_end_date >= start_date then 0 else 1 end) over (partition by id order by s_no) as grp
      from (select t.*,
                   max(end_date) over (
                       partition by id
                       order by start_date
                       rows between unbounded preceding and 1 preceding
                      ) as prev_end_date
            from t
           ) t
     ) t
group by id, grp;


ID
MIN(START_DATE)
MAX(END_DATE)
S_NOS


1
2020-10-11 00:00:00
2020-10-11 00:00:00
1

1
2020-11-04 00:00:00
2020-12-20 00:00:00
2,3,4,5

1
2021-01-01 00:00:00
2021-03-25 00:00:00
6,7


select *
from (select t.*,
             sum(case when prev_end_date >= start_date then 0 else 1 end) over (partition by id order by s_no) as grp
      from (select t.*,
                   max(end_date) over (
                       partition by id
                       order by start_date
                       rows between unbounded preceding and 1 preceding
                      ) as prev_end_date
            from t
           ) t
     ) t


S_NO
ID
START_DATE
END_DATE
PREV_END_DATE
GRP


1
1
2020-10-11 00:00:00
2020-10-11 00:00:00
null
1

2
1
2020-11-04 00:00:00
2020-12-11 00:00:00
2020-10-11 00:00:00
2

3
1
2020-11-05 00:00:00
2020-11-10 00:00:00
2020-12-11 00:00:00
2

4
1
2020-11-06 00:00:00
2020-11-10 00:00:00
2020-12-11 00:00:00
2

5
1
2020-11-20 00:00:00
2020-12-20 00:00:00
2020-12-11 00:00:00
2

6
1
2021-01-01 00:00:00
2021-01-20 00:00:00
2020-12-20 00:00:00
3

7
1
2021-01-01 00:00:00
2021-03-25 00:00:00
2021-01-20 00:00:00
3

ID	MIN(START_DATE)	MAX(END_DATE)	S_NOS
1	2020-10-11 00:00:00	2020-10-11 00:00:00	1
1	2020-11-04 00:00:00	2020-12-20 00:00:00	2,3,4,5
1	2021-01-01 00:00:00	2021-03-25 00:00:00	6,7

S_NO	ID	START_DATE	END_DATE	PREV_END_DATE	GRP
1	1	2020-10-11 00:00:00	2020-10-11 00:00:00	null	1
2	1	2020-11-04 00:00:00	2020-12-11 00:00:00	2020-10-11 00:00:00	2
3	1	2020-11-05 00:00:00	2020-11-10 00:00:00	2020-12-11 00:00:00	2
4	1	2020-11-06 00:00:00	2020-11-10 00:00:00	2020-12-11 00:00:00	2
5	1	2020-11-20 00:00:00	2020-12-20 00:00:00	2020-12-11 00:00:00	2
6	1	2021-01-01 00:00:00	2021-01-20 00:00:00	2020-12-20 00:00:00	3
7	1	2021-01-01 00:00:00	2021-03-25 00:00:00	2021-01-20 00:00:00	3