如何在Dynamic SQL中实现按当前月份倒序排列的滚动12个月工时汇总透视表

我来帮你搞定这个滚动12个月透视表的列排序问题！你的原代码里用DISTINCT提取月份会打乱排序逻辑，而且如果某个月没有工时数据，对应的列就会缺失。下面是调整后的完整方案，既能保证列从当前月份开始倒推12个月排列，还能确保所有12个月的列都存在（哪怕对应月份没有数据）：

核心思路 #

1. 先生成连续的过去12个月日期序列（从当前月份的第一天倒推11个月，加上当前月，共12个月），避免依赖表中现有数据的月份。
2. 按日期从新到旧的顺序生成透视列名称，确保列顺序符合需求。
3. 通过左连接让每个客户对应所有12个月的记录，缺失工时的月份可以显示为0（或者保留NULL）。

修改后的完整代码 #

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
DECLARE @currentMonthStart DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

-- 生成过去12个月的连续月份序列，按日期倒序排列
;WITH Last12Months AS (
    SELECT 
        DATEADD(MONTH, -n, @currentMonthStart) AS MonthStart
    FROM (
        VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)
    ) AS Numbers(n)
)
-- 生成按倒序排列的透视列名称
SELECT @cols = STUFF(
    (SELECT ',' + QUOTENAME(DATENAME(mm, MonthStart) + ' of ' + DATENAME(year, MonthStart))
     FROM Last12Months
     ORDER BY MonthStart DESC  -- 关键：按日期倒序生成列
     FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
)

-- 构造动态透视SQL，用左连接确保所有月份都显示
SET @query = '
WITH Last12Months AS (
    SELECT 
        DATEADD(MONTH, -n, ''' + CONVERT(VARCHAR(10), @currentMonthStart, 120) + ''') AS MonthStart
    FROM (
        VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)
    ) AS Numbers(n)
),
CustomerMonths AS (
    SELECT DISTINCT 
        t.CustName,
        lm.MonthStart,
        DATENAME(mm, lm.MonthStart) + '' of '' + DATENAME(year, lm.MonthStart) AS months_ago
    FROM [TimeEntryList] t
    CROSS JOIN Last12Months lm
)
SELECT 
    cm.CustName,
    ' + @cols + '
FROM (
    SELECT 
        cm.CustName,
        cm.months_ago,
        ISNULL(SUM(t.Hours), 0) AS NetQty  -- 把NULL转为0，可选
    FROM CustomerMonths cm
    LEFT JOIN [TimeEntryList] t 
        ON cm.CustName = t.CustName
        AND t.Date >= cm.MonthStart
        AND t.Date < DATEADD(MONTH, 1, cm.MonthStart)
    GROUP BY cm.CustName, cm.months_ago
) AS source
PIVOT (
    SUM(NetQty)
    FOR months_ago IN (' + @cols + ')
) AS PivotTable
ORDER BY CustName'

EXECUTE sp_executesql @query;

关键改进点说明 #

• 生成连续月份序列：用CTELast12Months生成过去12个月的第一天，不管表中有没有对应数据，都能保证12列完整显示。
• 控制列顺序：在生成@cols时通过ORDER BY MonthStart DESC确保列从当前月份开始，依次倒推到12个月前，彻底解决排序问题。
• 左连接补全缺失数据：通过CustomerMonthsCTE把客户和所有月份做交叉连接，再左连接工时表，这样某个客户在某月份没有工时的话，会显示为0（通过ISNULL处理），避免单元格空白。
• 日期范围匹配：用t.Date >= cm.MonthStart AND t.Date < DATEADD(MONTH, 1, cm.MonthStart)来精准匹配每个月的工时数据，比直接匹配月份名称更可靠。

如果不需要显示0，只需要把ISNULL(SUM(t.Hours), 0)改成SUM(t.Hours)即可，缺失数据会显示为NULL。

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