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