如何基于Doc_WarehouseApprovedDate将SQL列名改为[Year_month]格式

需要基于Doc_WarehouseApprovedDate字段设置结果列名，格式为[年份_月份]（例如[2024_03]），目前使用的SQL代码是固定的月份缩写列（Jan、Feb等），尝试多种方法后未找到简便实现方式，现有相关代码如下：

DECLARE @DateTo nvarchar(max) = '2024-03-31'

SELECT 
-- <此处为其他代码>
    CAST(SUM(ISNULL(Usage.[Jan],0)) as INT) as [Jan],   -- 1月用量
    CAST(SUM(ISNULL(Usage.[Feb],0)) as INT) as [Feb],   -- 2月用量
    CAST(SUM(ISNULL(Usage.[Mar],0)) as INT) as [Mar],   -- 3月用量
    CAST(SUM(ISNULL(Usage.[Apr],0)) as INT) as [Apr],   -- 4月用量
    CAST(SUM(ISNULL(Usage.[May],0)) as INT) as [May],   -- 5月用量
    CAST(SUM(ISNULL(Usage.[Jun],0)) as INT) as [Jun],   -- 6月用量
    CAST(SUM(ISNULL(Usage.[Jul],0)) as INT) as [Jul],   -- 7月用量
    CAST(SUM(ISNULL(Usage.[Aug],0)) as INT) as [Aug],   -- 8月用量
    CAST(SUM(ISNULL(Usage.[Sep],0)) as INT) as [Sep],   -- 9月用量
    CAST(SUM(ISNULL(Usage.[Oct],0)) as INT) as [Oct],   -- 10月用量
    CAST(SUM(ISNULL(Usage.[Nov],0)) as INT) as [Nov],   -- 11月用量
    CAST(SUM(ISNULL(Usage.[Dec],0)) as INT) as [Dec]    -- 12月用量
FROM 
    Items   -- 产品和零件表
-- <此处为其他代码>
LEFT JOIN 
    (SELECT 
        Del_ItmID, 
        Warehouse.DIC_IntColumn1,
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 1 THEN Del_Amount ELSE 0 END) AS [Jan],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 2 THEN Del_Amount ELSE 0 END) AS [Feb],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 3 THEN Del_Amount ELSE 0 END) AS [Mar],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 4 THEN Del_Amount ELSE 0 END) AS [Apr],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 5 THEN Del_Amount ELSE 0 END) AS [May],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 6 THEN Del_Amount ELSE 0 END) AS [Jun],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 7 THEN Del_Amount ELSE 0 END) AS [Jul],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 8 THEN Del_Amount ELSE 0 END) AS [Aug],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 9 THEN Del_Amount ELSE 0 END) AS [Sep],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 10 THEN Del_Amount ELSE 0 END) AS [Oct],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 11 THEN Del_Amount ELSE 0 END) AS [Nov],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 12 THEN Del_Amount ELSE 0 END) AS [Dec]
-- <此处为其他代码>
    ) as Usage ON Usage.Del_ItmID = Items.Itm_Id 
        AND Usage.DIC_IntColumn1 = Stock.DIC_IntColumn1 -- 文档中对应同一产品的条目

SQL静态查询无法动态定义列名，需使用动态SQL生成对应格式的列名。以下是基于@DateTo指定年份生成[YYYY_MM]格式列名的实现：

步骤1：提取目标年份 #

从@DateTo中提取年份，用于生成列名前缀：

DECLARE @TargetYear INT = YEAR(CAST(@DateTo AS DATE))

步骤2：动态生成并执行查询语句 #

通过字符串拼接生成包含[YYYY_01]到[YYYY_12]列名的SQL语句，保留原有聚合逻辑并执行：

DECLARE @DynamicSQL NVARCHAR(MAX)

SET @DynamicSQL = N'
SELECT 
    -- <此处保留原其他代码>
    CAST(SUM(ISNULL(Usage.[Jan],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_01],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年1月用量
    CAST(SUM(ISNULL(Usage.[Feb],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_02],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年2月用量
    CAST(SUM(ISNULL(Usage.[Mar],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_03],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年3月用量
    CAST(SUM(ISNULL(Usage.[Apr],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_04],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年4月用量
    CAST(SUM(ISNULL(Usage.[May],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_05],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年5月用量
    CAST(SUM(ISNULL(Usage.[Jun],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_06],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年6月用量
    CAST(SUM(ISNULL(Usage.[Jul],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_07],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年7月用量
    CAST(SUM(ISNULL(Usage.[Aug],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_08],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年8月用量
    CAST(SUM(ISNULL(Usage.[Sep],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_09],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年9月用量
    CAST(SUM(ISNULL(Usage.[Oct],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_10],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年10月用量
    CAST(SUM(ISNULL(Usage.[Nov],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_11],   -- ' + CAST(@TargetYear AS NVARCHAR) + '年11月用量
    CAST(SUM(ISNULL(Usage.[Dec],0)) AS INT) AS [' + CAST(@TargetYear AS NVARCHAR) + '_12]    -- ' + CAST(@TargetYear AS NVARCHAR) + '年12月用量
FROM 
    Items   -- 产品和零件表
-- <此处保留原其他代码>
LEFT JOIN 
    (SELECT 
        Del_ItmID, 
        Warehouse.DIC_IntColumn1,
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 1 THEN Del_Amount ELSE 0 END) AS [Jan],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 2 THEN Del_Amount ELSE 0 END) AS [Feb],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 3 THEN Del_Amount ELSE 0 END) AS [Mar],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 4 THEN Del_Amount ELSE 0 END) AS [Apr],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 5 THEN Del_Amount ELSE 0 END) AS [May],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 6 THEN Del_Amount ELSE 0 END) AS [Jun],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 7 THEN Del_Amount ELSE 0 END) AS [Jul],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 8 THEN Del_Amount ELSE 0 END) AS [Aug],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 9 THEN Del_Amount ELSE 0 END) AS [Sep],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 10 THEN Del_Amount ELSE 0 END) AS [Oct],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 11 THEN Del_Amount ELSE 0 END) AS [Nov],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 12 THEN Del_Amount ELSE 0 END) AS [Dec]
-- <此处保留原其他代码>
    ) as Usage ON Usage.Del_ItmID = Items.Itm_Id 
        AND Usage.DIC_IntColumn1 = Stock.DIC_IntColumn1 -- 文档中对应同一产品的条目
'

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL

说明 #

• 动态SQL会根据@DateTo的年份自动生成[YYYY_MM]格式的列名，比如@DateTo为2024-03-31时，列名会是[2024_01]到[2024_12]
• 子查询中仍使用月份缩写作为临时列名，外层通过动态拼接替换为目标格式
• 若需要覆盖多个年份的列名，可扩展逻辑生成对应年份的月份列

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