SQL Server 2022

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

CREATE TABLE Example 
(
    Project	VARCHAR(512),
    ScenarioStart	VARCHAR(512),
    ScenarioEnd	VARCHAR(512),
    TimePeriods	VARCHAR(512)
);

INSERT INTO Example (Project, ScenarioStart, ScenarioEnd) VALUES
	('1', '2024M9', '2025M3'),
	('2', '2024M6', '2024M10');

SELECT * FROM Example;

-- Recursive CTE to generate a list of months within the date range:
WITH Months AS (
    SELECT Example.Project, CONVERT(DATE, DATEADD(D, -(DAY(CONVERT(Date,  Substring(ScenarioStart,1,4) + '-' + Substring(ScenarioStart,6,2) + '-1'))) + 1, CONVERT(Date,  Substring(ScenarioStart,1,4) + '-' + Substring(ScenarioStart,6,2) + '-1'))) [MonthDate]
    FROM Example
    UNION ALL
    SELECT Example.Project, DATEADD(M, 1, MonthDate)
    FROM Months   
    INNER JOIN Example on Example.Project=Months.Project
    WHERE MonthDate <= DATEADD(M, -1, CONVERT(Date,Substring(Example.ScenarioEnd,1,4) + '-' + Substring(Example.ScenarioEnd,6,2) + '-1'))
)
UPDATE Example
  SET Example.TimePeriods=t.TimePeriods
FROM Example E
INNER JOIN
(
  --Combine MonthDates using STRING_AGG to make TimePeriods 
  SELECT Project, STRING_AGG(CAST(Year(MONTHDATE)  as Varchar(4))+'M'+Cast(Month(Monthdate) as varchar(2)), '|') as TimePeriods 
  FROM Months
  GROUP BY Project 
) t
ON E.Project=t.Project

SELECT * FROM Example;

Project	ScenarioStart	ScenarioEnd	TimePeriods
1	2024M9	2025M3	null
2	2024M6	2024M10	null

Project	ScenarioStart	ScenarioEnd	TimePeriods
1	2024M9	2025M3	2024M9\|2024M10\|2024M11\|2024M12\|2025M1\|2025M2\|2025M3
2	2024M6	2024M10	2024M6\|2024M7\|2024M8\|2024M9\|2024M10