Oracle 18c

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

CREATE TABLE road_inspections (road_id, year, cond) as

select 1, 2009, 17 from dual union all

select 1, 2011, 16 from dual union all

select 1, 2015, 14 from dual union all

select 1, 2016, 18.3 from dual union all

select 1, 2019, 18.1 from dual union all

select 2, 2013, 17.5 from dual union all

select 2, 2016, 18 from dual union all

select 2, 2019, 18 from dual union all

select 2, 2022, 18 from dual union all

select 3, 2022, 20 from dual;

10 rows affected

SELECT r.road_id,

y.year,

r.cond

FROM ( SELECT r.*,

LEAD(year, 1, EXTRACT(YEAR FROM SYSDATE) + 1)

OVER (PARTITION BY road_id ORDER BY year) AS next_year

FROM road_inspections r

) r

CROSS JOIN LATERAL (

SELECT r.year + LEVEL - 1 AS year

FROM DUAL

CONNECT BY r.year + LEVEL - 1 < r.next_year

) y