By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( sno, 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( dt ) AS start_date,
MAX( dt ) AS end_date,
LISTAGG( CASE value WHEN 1 THEN sno END, ',' )
WITHIN GROUP ( ORDER BY dt ) AS snos
FROM (
SELECT sno,
id,
dt,
value,
SUM( start_end ) OVER ( ORDER BY dt ASC, value DESC )
AS grp
FROM (
SELECT sno,
id,
dt,
value,
CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 1
WHEN 0 THEN 0
ELSE NULL
END AS start_end
FROM table_name
UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
)
)
GROUP BY id, grp;
| ID | START_DATE | END_DATE | SNOS |
|---|---|---|---|
| 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 |