如何在SQL中生成两日期列间月份名称拼接的新列？

针对你的需求，下面是基于SQL Server的实现方案，能直接生成包含月份名称拼接列的结果：

实现思路 #

1. 先把EndDate为空的情况替换成当年12月31日，保证日期范围完整
2. 给每个ID生成从StartDate所在月份到结束月份的所有月份列表
3. 把每个ID对应的月份名称拼合成一个字符串

完整SQL代码（适用于SQL Server 2017+） #

WITH MonthCTE AS (
    SELECT 
        ID,
        StartDate,
        -- 处理EndDate为NULL的情况，自动用当年12月31日替代
        ISNULL(EndDate, DATEFROMPARTS(YEAR(StartDate), 12, 31)) AS EndDate,
        -- 取StartDate所在月份的第一天，方便后续递推
        DATEFROMPARTS(YEAR(StartDate), MONTH(StartDate), 1) AS CurrentMonth
    FROM OfferTest
    UNION ALL
    SELECT 
        ID,
        StartDate,
        EndDate,
        -- 递推生成下一个月的第一天
        DATEADD(MONTH, 1, CurrentMonth) AS CurrentMonth
    FROM MonthCTE
    -- 当当前月份还没到结束月份时继续递推
    WHERE CurrentMonth < DATEFROMPARTS(YEAR(EndDate), MONTH(EndDate), 1)
)
SELECT 
    ot.ID,
    ot.StartDate,
    ot.EndDate,
    -- 把每个ID的月份名称用逗号拼接起来
    STRING_AGG(DATENAME(MONTH, CurrentMonth), ', ') AS MonthNames
FROM OfferTest ot
JOIN MonthCTE mc ON ot.ID = mc.ID
GROUP BY ot.ID, ot.StartDate, ot.EndDate
ORDER BY ot.ID;

测试结果 #

用你提供的测试数据执行后，会得到这样的结果：

兼容低版本SQL Server（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，可以用下面的代码替代：

WITH MonthCTE AS (
    SELECT 
        ID,
        StartDate,
        ISNULL(EndDate, DATEFROMPARTS(YEAR(StartDate), 12, 31)) AS EndDate,
        DATEFROMPARTS(YEAR(StartDate), MONTH(StartDate), 1) AS CurrentMonth
    FROM OfferTest
    UNION ALL
    SELECT 
        ID,
        StartDate,
        EndDate,
        DATEADD(MONTH, 1, CurrentMonth) AS CurrentMonth
    FROM MonthCTE
    WHERE CurrentMonth < DATEFROMPARTS(YEAR(EndDate), MONTH(EndDate), 1)
)
SELECT 
    ot.ID,
    ot.StartDate,
    ot.EndDate,
    -- 用STUFF+XML的方式拼接字符串
    STUFF((
        SELECT ', ' + DATENAME(MONTH, mc.CurrentMonth)
        FROM MonthCTE mc
        WHERE mc.ID = ot.ID
        ORDER BY mc.CurrentMonth
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS MonthNames
FROM OfferTest ot
GROUP BY ot.ID, ot.StartDate, ot.EndDate
ORDER BY ot.ID;

小提示 #

• 要是需要中文月份名称，在中文操作系统的SQL Server里直接用DATENAME(MONTH, CurrentMonth)就能返回中文；如果是英文环境，可以切换语言后再提取。
• 递归CTE里的日期处理都是基于月份第一天，能避免跨月的日期问题，确保所有中间月份都被包含。

内容的提问来源于stack exchange，提问作者PRI