For each row in a t-sql query resultset, get the startdate and enddate and extract months between, then union the results as one list of months
If you have a "Tally" or "Nums" function, this becomes child's play.
SELECT Months = CONVERT(CHAR(6),DATEADD(mm,t.N,StartDate),112)
FROM dbo.ProductTable
CROSS APPLY dbo.fnTally(0,DATEDIFF(mm,StartDate,EndDate))t
WHERE Site = 'X'
AND Product_ID = '1'
ORDER BY Site,Product_ID,Months --Just in case we expand on this later.
;
It also consumes 1 read instead of 64 like the rCTE method does, which is also slower than a While Loop.
I know a lot of people don't care about that kind of performance for such small sets of data but that's also how they end up with a slow server due to "Death by a Thousand Cuts".
You can search the web for such a function but I can save you some time by posting the link to the one I use. I know the author. :D
https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally