最简单的方法是使用一个辅助日历表，在两个表之间建立一个简单的连接：

select distinct a.id, convert(char(7), b.[Date], 120) as month
from yourTable as a
join Calendar as b
    on a.DateFrom <= b.[Date]
    and a.DateTo >= b.[Date]
;

如果没有日历表，可能需要考虑添加一个日历表。
如果不能（或不想）添加日历表，可以使用数字（tally）表获得相同的结果。
如果没有计数表，可以使用公共表表达式动态生成一个。
在这种情况下，sql是这样的：

WITH N10 AS
(
    SELECT n
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))V(n)
), Tally AS
(
    SELECT TOP (SELECT MAX(DATEDIFF(MONTH, DateFrom, DateTo)) + 1 FROM yourTable) ROW_NUMBER() OVER(ORDER BY @@SPID) -1 As n
    FROM N10 As Ten
    CROSS JOIN N10 As Hundred
    --CROSS JOIN N10 As Thousand
)

SELECT [ID], CONVERT(char(7), Months, 120) As Months
FROM yourTable
CROSS APPLY 
(
    SELECT N, DATEADD(MONTH, N, [DateFrom]) As Months
    FROM Tally 
) X
WHERE Months <= EOMONTH([DateTo])
ORDER BY Id, Months

假设您的表名为“dataval”，请尝试以下操作

;WITH minmax AS (
     SELECT Min(Eomonth(datefrom)) AS MinDate, 
            Max(Eomonth(dateto))   AS MaxDate 
     FROM   dataval), 
 months (date) AS (
     SELECT mindate 
     FROM   minmax 
     UNION ALL 
     SELECT Dateadd(month, 1, date) 
     FROM   months 
     WHERE  Dateadd(month, 1, date) <= (SELECT maxdate FROM minmax)) 
 SELECT ID, FORMAT (date, 'yyyy-MM') Months 
 FROM   dataval 
 INNER JOIN months 
    ON months.date BETWEEN Eomonth(dataval.datefrom) AND Eomonth(dataval.dateto)

在这里摆弄