SQL Server存储过程动态生成年月列 无固定列数行转列

核心原理：SQL的查询结构在编译阶段就会固定，无法通过静态SQL直接把字符串变量里的内容解析为独立列，必须通过动态SQL完成运行时的查询结构拼接与执行，不需要依赖PIVOT，也不需要提前声明固定数量的列变量。

实现步骤 #

• 生成日期区间内所有合法年月值，按规范处理列名避免特殊字符报错
• 拼接完整的SELECT子句，每个年月对应一个独立列，列值直接设为对应年月文本
• 调用sp_executesql执行拼接好的动态SQL，返回1行结果，列数与区间内月数完全匹配

可直接复用的存储过程代码 #

CREATE OR ALTER PROCEDURE dbo.GetDynamicPeriodColumns
    @datefrom DATE,
    @dateto DATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 清理可能存在的同名临时表
    IF OBJECT_ID('tempdb..#periodesKOP') IS NOT NULL DROP TABLE #periodesKOP;

    -- 递归生成日期区间内所有月份的第一天
    WITH MonthSeq AS (
        SELECT DATEFROMPARTS(YEAR(@datefrom), MONTH(@datefrom), 1) AS MonthStart
        UNION ALL
        SELECT DATEADD(MONTH, 1, MonthStart)
        FROM MonthSeq
        WHERE MonthStart < DATEFROMPARTS(YEAR(@dateto), MONTH(@dateto), 1)
    )
    SELECT
        MonthStart,
        -- 生成带方括号的合法列名，格式为[yyyyMM]
        QUOTENAME(CONVERT(VARCHAR(6), MonthStart, 112)) AS ColName,
        -- 生成列显示值，格式为yyyy-MM
        CONVERT(VARCHAR(7), MonthStart, 120) AS ColValue
    INTO #periodesKOP
    FROM MonthSeq
    -- 递归层级设为1000，支持最多83年的月份跨度，覆盖绝大多数业务场景
    OPTION (MAXRECURSION 1000);

    DECLARE @selectCols NVARCHAR(MAX), @execSql NVARCHAR(MAX);

    -- 拼接SELECT子句，高版本SQL Server可用STRING_AGG简化写法
    SELECT @selectCols = STRING_AGG(CAST(ColName + N' = N''' + ColValue + N'''' AS NVARCHAR(MAX)), N', ')
    FROM #periodesKOP
    ORDER BY MonthStart;

    -- 兼容SQL Server 2016及更早无STRING_AGG的版本，用STUFF+FOR XML PATH写法
    -- SELECT @selectCols = STUFF(
    --     (SELECT N', ' + ColName + N' = N''' + ColValue + N''''
    --      FROM #periodesKOP
    --      ORDER BY MonthStart
    --      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,N''
    -- );

    -- 拼接完整查询语句并执行
    SET @execSql = N'SELECT ' + @selectCols;
    EXEC sp_executesql @execSql;
END
GO

方案优势 #

• 无固定列数限制：仅受SQL Server单查询最多1024列的原生硬限制，对应跨度超85年，常规业务场景完全不会触达上限
• 无冗余空列：区间内有多少个月就生成多少列，不会出现硬编码30列时的空列问题
• 逻辑轻量：不需要PIVOT聚合，不需要关联业务表，直接通过常量赋值生成列值，执行效率高
• 兼容已有逻辑：之前实现的#periodesKOP临时表、@cols拼接逻辑都可以直接复用，只需要补充列值赋值的部分即可

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