使用动态SQL的这种方法怎么样：

CREATE TABLE Data ( ID INT, Name VARCHAR(100), StartDate DATE, EndDate DATE )
INSERT Data
VALUES
    ('1', 'Aa', '2021-10-14', '2021-12-22'),
    ('2', 'Ab', '2021-12-02', '2022-10-05')

-- Extract overall date range
DECLARE @MinStartDate DATE, @MaxEndDate AS DATE
SELECT @MinStartDate = MIN(StartDate), @MaxEndDate =  MAX(EndDate)
FROM Data

-- Convert to months (DATETRUNC() may be used instead with SQL Server 2022 and later)
DECLARE @StartMonth DATE = DATEADD(day, 1 - DAY(@MinStartDate), @MinStartDate)
DECLARE @EndMonth DATE = DATEADD(day, 1 - DAY(@MaxEndDate), @MaxEndDate)

-- Generate calendar of months within range
DECLARE @Months TABLE ( Month DATE)
;WITH Months AS (
   SELECT @StartMonth AS Month
   UNION ALL
   SELECT DATEADD(month, 1, M.Month)
   FROM Months M
   WHERE M.Month < @EndMonth
)
INSERT @Months
SELECT M.Month
FROM Months M

-- Define SQL Templates
DECLARE @SqlTemplate VARCHAR(MAX) = '
SELECT ID, Name, StartDate, EndDate
<ColumnSql>
FROM Data D
ORDER BY D.Name
'

DECLARE @ColumnTemplate VARCHAR(MAX) = '
    , CASE WHEN D.StartDate <= <MonthEnd> AND <MonthStart> <= D.EndDate THEN 1 ELSE 0 END AS <ColumnName>'

-- Build month-specific column select items from template
DECLARE @ColumnSql VARCHAR(MAX) = (
    SELECT STRING_AGG(C.ColumnSql, '') WITHIN GROUP(ORDER BY M.Month)
    FROM @Months M
    CROSS APPLY (
       SELECT
            CONVERT(CHAR(6), M.Month, 112) AS ColumnName,
            M.Month AS MonthStart,
            EOMONTH(M.Month) AS MonthEnd
    ) MD
    CROSS APPLY (
        SELECT REPLACE(REPLACE(REPLACE(
            @ColumnTemplate
            , '<ColumnName>', QUOTENAME(MD.ColumnName))
            , '<MonthStart>', QUOTENAME(CONVERT(CHAR(8), MD.MonthStart, 112), ''''))
            , '<MonthEnd>', QUOTENAME(CONVERT(CHAR(8), MD.MonthEnd, 112), ''''))
            AS ColumnSql
    ) C
)

--SELECT @ColumnSql

-- Build final SQL
DECLARE @Sql VARCHAR(MAX) = REPLACE(@SqlTemplate, '<ColumnSql>', @ColumnSql)

SELECT @Sql

-- Deliver
EXEC (@Sql)

生成的SQL：

SELECT ID, Name, StartDate, EndDate

    , CASE WHEN D.StartDate <= '20211031' AND D.EndDate >= '20211001' THEN 1 ELSE 0 END AS [202110]
    , CASE WHEN D.StartDate <= '20211130' AND D.EndDate >= '20211101' THEN 1 ELSE 0 END AS [202111]
    , CASE WHEN D.StartDate <= '20211231' AND D.EndDate >= '20211201' THEN 1 ELSE 0 END AS [202112]
    , CASE WHEN D.StartDate <= '20220131' AND D.EndDate >= '20220101' THEN 1 ELSE 0 END AS [202201]
    , CASE WHEN D.StartDate <= '20220228' AND D.EndDate >= '20220201' THEN 1 ELSE 0 END AS [202202]
    , CASE WHEN D.StartDate <= '20220331' AND D.EndDate >= '20220301' THEN 1 ELSE 0 END AS [202203]
    , CASE WHEN D.StartDate <= '20220430' AND D.EndDate >= '20220401' THEN 1 ELSE 0 END AS [202204]
    , CASE WHEN D.StartDate <= '20220531' AND D.EndDate >= '20220501' THEN 1 ELSE 0 END AS [202205]
    , CASE WHEN D.StartDate <= '20220630' AND D.EndDate >= '20220601' THEN 1 ELSE 0 END AS [202206]
    , CASE WHEN D.StartDate <= '20220731' AND D.EndDate >= '20220701' THEN 1 ELSE 0 END AS [202207]
    , CASE WHEN D.StartDate <= '20220831' AND D.EndDate >= '20220801' THEN 1 ELSE 0 END AS [202208]
    , CASE WHEN D.StartDate <= '20220930' AND D.EndDate >= '20220901' THEN 1 ELSE 0 END AS [202209]
    , CASE WHEN D.StartDate <= '20221031' AND D.EndDate >= '20221001' THEN 1 ELSE 0 END AS [202210]
FROM Data D
ORDER BY D.Name

结果：
| 识别号|姓名|开始日期|结束日期|小行星202|小行星202|小行星202|小行星202201|小行星202202|小行星202|小行星202|小行星202|小行星202|小行星202|小行星202|小行星202|小行星202210|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 1个|阿|二〇二一年十月十四日|二○二一年十二月二十二日|1个|1个|1个|无|无|无|无|无|无|无|无|无|无|
| 第二章|抗体|二○二一年十二月二日|二〇二二年十月五日|无|无|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|1个|
参见this db<>fiddle。
上面使用了"start1〈= end2 AND start2〈= end1"的日期范围重叠的标准测试，它假设所有日期都包含在内。