Oracle 18c

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

CREATE TABLE table_name (id, val, eff_date, end_date) AS
SELECT 10, 100, DATE '2021-01-01', DATE '2021-01-04' FROM DUAL UNION ALL
SELECT 10, 105, DATE '2021-01-05', DATE '2021-01-07' FROM DUAL UNION ALL
SELECT 10, 100, DATE '2021-01-08', DATE '2021-01-10' FROM DUAL UNION ALL
SELECT 10, 100, DATE '2021-01-11', DATE '2021-01-17' FROM DUAL UNION ALL
SELECT 10, 100, DATE '2021-01-18', DATE '2021-01-21' FROM DUAL UNION ALL
SELECT 10, 110, DATE '2021-01-22', null FROM DUAL;
6 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT id,
       val,
       MIN(eff_date) AS eff_date,
       MAX(end_date) AS end_date
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY eff_date)
           - ROW_NUMBER() OVER (PARTITION BY id, val ORDER BY eff_date) AS grp
  FROM   table_name t
)
GROUP BY id, val, grp
ORDER BY id, eff_date;


ID
VAL
EFF_DATE
END_DATE


10
100
2021-01-01 00:00:00
2021-01-04 00:00:00

10
105
2021-01-05 00:00:00
2021-01-07 00:00:00

10
100
2021-01-08 00:00:00
2021-01-21 00:00:00

10
110
2021-01-22 00:00:00
null


SELECT *
FROM   table_name t
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER BY eff_date
  MEASURES
    FIRST(val) AS val,
    FIRST(eff_date) AS eff_date,
    LAST(end_date) AS end_date
  PATTERN (same_val+)
  DEFINE same_val AS FIRST(val) = val
)


ID
VAL
EFF_DATE
END_DATE


10
100
2021-01-01 00:00:00
2021-01-04 00:00:00

10
105
2021-01-05 00:00:00
2021-01-07 00:00:00

10
100
2021-01-08 00:00:00
2021-01-21 00:00:00

10
110
2021-01-22 00:00:00
null

ID	VAL	EFF_DATE	END_DATE
10	100	2021-01-01 00:00:00	2021-01-04 00:00:00
10	105	2021-01-05 00:00:00	2021-01-07 00:00:00
10	100	2021-01-08 00:00:00	2021-01-21 00:00:00
10	110	2021-01-22 00:00:00	null