SQL Server 2022

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

create table projects (ProjectId int,ScenarioStart varchar(7),ScenarioEnd varchar(7),TimePeriods varchar(1000));
insert into projects (ProjectId,ScenarioStart,Scenarioend) values
 (1,'2024M9','2025M3')        
,(2,'2024M6','2024M10')
,(3,'2024M6','2025M10')
;
select * from projects;


ProjectId
ScenarioStart
ScenarioEnd
TimePeriods


1
2024M9
2025M3
null

2
2024M6
2024M10
null

3
2024M6
2025M10
null


with t1 as(
  select * ,(y2-y1)*12+m2-m1 project_lng 
  from (
    select *
     ,cast(substring(ScenarioStart,1,4) as int) y1
     ,cast(substring(ScenarioStart,6,2) as int) m1
     ,cast(substring(ScenarioEnd,1,4) as int) y2
     ,cast(substring(ScenarioEnd,6,2) as int) m2
from projects) t
)
,expanded_periods as(
select *
  ,y1+(m1+n-1)/12 yy
  ,(13-(12-(m1+n-1)%12)) mm
from t1 
cross apply (select value n from generate_series(0,t1.project_lng,1) s1)nn
)
update projects
  set TimePeriods=TimePeriodsNew
from (
select ProjectId -- ,ScenarioStart,ScenarioEnd
  ,string_agg(concat(yy,'M',mm),'|')within group (order by yy,mm) TimePeriodsNew
from expanded_periods
group by ProjectId -- ,ScenarioStart,ScenarioEnd
)src
where src.ProjectId=projects.ProjectId
-- select * from expanded_periods
-- order by projectId,n
3 rows affected

select * from projects


ProjectId
ScenarioStart
ScenarioEnd
TimePeriods


1
2024M9
2025M3
2024M9|2024M10|2024M11|2024M12|2025M1|2025M2|2025M3

2
2024M6
2024M10
2024M6|2024M7|2024M8|2024M9|2024M10

3
2024M6
2025M10
2024M6|2024M7|2024M8|2024M9|2024M10|2024M11|2024M12|2025M1|2025M2|2025M3|2025M4|2025M5|2025M6|2025M7|2025M8|2025M9|2025M10

ProjectId	ScenarioStart	ScenarioEnd	TimePeriods
1	2024M9	2025M3	2024M9\|2024M10\|2024M11\|2024M12\|2025M1\|2025M2\|2025M3
2	2024M6	2024M10	2024M6\|2024M7\|2024M8\|2024M9\|2024M10
3	2024M6	2025M10	2024M6\|2024M7\|2024M8\|2024M9\|2024M10\|2024M11\|2024M12\|2025M1\|2025M2\|2025M3\|2025M4\|2025M5\|2025M6\|2025M7\|2025M8\|2025M9\|2025M10