Oracle 21c

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

with cte_data (id, col_2010, col_2011, col_2012, col_2013, col_2014, col_2015, col_2016, col_2017, col_2018, col_2019, col_2020, col_2021) AS
(
select 1, 0, 1, 2, 3, 0, 1, 2, 0, 4, 5, 0, 0 from dual union all
select 2, 0, 7, 8, 8, 9, 0, 0, 0, 0, 0, 0, 0 from dual
)
  , cte_upvt (id, year, val) as
(
 select id, year, val
   from cte_data
unpivot ( val for year in ( col_2010 as 2010, col_2011 as 2011
                          , col_2012 as 2012, col_2013 as 2013
                          , col_2014 as 2014, col_2015 as 2015
                          , col_2016 as 2016, col_2017 as 2017
                          , col_2018 as 2018, col_2019 as 2019
                          , col_2020 as 2020, col_2021 as 2021
                          )
        )
)
  ,  cte_reset (id, year, val, rst) as
(
select id, year, val
     , count(case val when 0 then 1 end) over(partition by id order by year asc)
  from cte_upvt
)
  ,  cte_grp (id, year, val, rst, grp) as
(
select id, year, val, rst
     , case when count(case when val > 0 then 1 end) over(partition by id, rst order by year asc) > 0 then 1 end
  from cte_reset
)
  select id
       , min(year)                                         as year_deb
       , max(year)                                         as year_fin
       , listagg(val, ',') within group(order by year asc) as lst_val
    from cte_grp
   where grp = 1
group by id, rst
order by id, rst;

ID	YEAR_DEB	YEAR_FIN	LST_VAL
1	2011	2013	1,2,3
1	2015	2016	1,2
1	2018	2019	4,5
2	2011	2014	7,8,8,9