MSSQL SELECT查询动态生成日期列 按月统计订购及收费金额

方案1：静态聚合（适用固定统计时间范围场景） #

如果你的统计月份范围是固定的，直接用CASE WHEN聚合即可，语法稳定可直接作为子查询关联其他表：

SELECT 
    vID,
    SUM(CASE WHEN month_col = 'JAN2021O' THEN value ELSE 0 END) AS JAN2021O,
    SUM(CASE WHEN month_col = 'JAN2021C' THEN value ELSE 0 END) AS JAN2021C,
    SUM(CASE WHEN month_col = 'FEB2021O' THEN value ELSE 0 END) AS FEB2021O,
    SUM(CASE WHEN month_col = 'FEB2021C' THEN value ELSE 0 END) AS FEB2021C,
    SUM(CASE WHEN month_col = 'MAR2022O' THEN value ELSE 0 END) AS MAR2022O,
    SUM(CASE WHEN month_col = 'MAR2022C' THEN value ELSE 0 END) AS MAR2022C
FROM (
    SELECT 
        vID,
        value,
        -- 生成列名：3位月份缩写+年份+类型
        LEFT(UPPER(DATENAME(MONTH, CONVERT(DATE, [date], 103))), 3) 
        + CAST(YEAR(CONVERT(DATE, [date], 103)) AS VARCHAR(4)) 
        + UPPER(type) AS month_col
    FROM 你的业务表名
) AS t
GROUP BY vID

方案2：动态PIVOT（适用自动匹配所有存在数据月份场景） #

如果需要自动识别表中所有存在数据的月份生成列，用动态SQL实现：

DECLARE 
    @column_list NVARCHAR(MAX),
    @dynamic_sql NVARCHAR(MAX)

-- 第一步：生成所有需要的列名列表
SELECT @column_list = COALESCE(@column_list + ', ', '') + QUOTENAME(month_col)
FROM (
    SELECT DISTINCT 
        LEFT(UPPER(DATENAME(MONTH, CONVERT(DATE, [date], 103))), 3) 
        + CAST(YEAR(CONVERT(DATE, [date], 103)) AS VARCHAR(4)) 
        + UPPER(type) AS month_col,
        -- 用于列排序的时间维度
        DATEFROMPARTS(YEAR(CONVERT(DATE, [date], 103)), MONTH(CONVERT(DATE, [date], 103)), 1) AS sort_date,
        UPPER(type) AS sort_type
    FROM 你的业务表名
    -- 可在此处添加时间范围过滤条件，比如WHERE [date] >= '2021-01-01'
) AS t
-- 按时间先后排序，同一月份O列在前C列在后
ORDER BY sort_date ASC, sort_type DESC

-- 第二步：拼接完整PIVOT查询语句
SET @dynamic_sql = N'
SELECT *
FROM (
    SELECT 
        vID,
        value,
        LEFT(UPPER(DATENAME(MONTH, CONVERT(DATE, [date], 103))), 3) 
        + CAST(YEAR(CONVERT(DATE, [date], 103)) AS VARCHAR(4)) 
        + UPPER(type) AS month_col
    FROM 你的业务表名
) AS source_data
PIVOT (
    SUM(value) -- 按vID+月份+类型汇总金额
    FOR month_col IN (' + @column_list + N')
) AS pivot_result'

-- 执行动态查询
EXEC sp_executesql @dynamic_sql

注意事项 #

• 代码中CONVERT(DATE, [date], 103)是适配样例中dd.mm.yyyy格式的字符串日期，如果你的表中date字段本身是DATE/DATETIME类型，可直接去掉转换逻辑使用字段本身。
• 空值默认返回NULL，如果需要显示为0，可以在生成@column_list时同步拼接ISNULL(列名, 0) AS 列名的逻辑。

关联其他查询的方法 #

静态方案关联 #

直接将整个聚合查询作为子查询，和其他表做JOIN即可：

SELECT * 
FROM 你的其他查询表 o
JOIN (
    -- 上面的静态聚合查询完整代码
) AS p ON o.vID = p.vID

动态方案关联 #

修改动态SQL，将PIVOT结果写入临时表后再做关联：

-- 动态SQL调整为将结果写入临时表
SET @dynamic_sql = N'
SELECT * INTO #pivot_result
FROM (
    SELECT 
        vID,
        value,
        LEFT(UPPER(DATENAME(MONTH, CONVERT(DATE, [date], 103))), 3) 
        + CAST(YEAR(CONVERT(DATE, [date], 103)) AS VARCHAR(4)) 
        + UPPER(type) AS month_col
    FROM 你的业务表名
) AS source_data
PIVOT (
    SUM(value)
    FOR month_col IN (' + @column_list + N')
) AS pivot_result

-- 关联其他表
SELECT * FROM #pivot_result p
JOIN 你的其他查询表 o ON p.vID = o.vID'

EXEC sp_executesql @dynamic_sql

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