如何实现带动态日期的Sum值Pivot交叉表？

问题需求 #

创建可接收动态日期的交叉表，展示指定用户在各对应日期的myValue求和值，期望输出格式如下：

UID    2022-11-09    2022-11-08    2022-11-07
12     420           400           350
15     100           50            0

现有代码问题分析 #

你当前的代码存在几个关键问题：

• 子查询仅按UID分组，未关联日期维度，无法生成每个用户对应各日期的聚合值
• WHERE条件中tDate <= @cols逻辑错误，@cols是动态列名集合，不是单个日期值
• Pivot子句中使用max(myValue)不符合求和需求，且源数据未包含tDate字段，无法完成按日期转列

修正后的完整实现代码 #

/* 创建临时表存储指定日期 */
CREATE TABLE #tmpDates
( tDate date )

INSERT into #tmpDates
VALUES
    ('2022-11-09'),
    ('2022-11-08'),
    ('2022-11-07')

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @colsWithIsNull AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

-- 生成动态列名（带方括号）
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(tDate) 
                      FROM #tmpDates
                      ORDER BY tDate DESC
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 生成处理NULL为0的列表达式（用于最终查询替换NULL）
SELECT @colsWithIsNull = STUFF((SELECT ', ISNULL(' + QUOTENAME(tDate) + ', 0) AS ' + QUOTENAME(tDate)
                                FROM #tmpDates
                                ORDER BY tDate DESC
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 构建动态查询语句
SET @query = 
N'
    SELECT UID, ' + @colsWithIsNull + '
    FROM (
        -- 关联临时表，获取每个用户在指定日期的求和值
        SELECT 
            t.UID,
            d.tDate,
            SUM(CASE WHEN t.tDate = d.tDate THEN t.myValue ELSE 0 END) AS sumValue
        FROM dbo.tblTrans t
        RIGHT JOIN #tmpDates d ON t.tDate = d.tDate
        WHERE t.myValue > 0 OR t.myValue IS NULL -- 保留无数据的日期行
        GROUP BY t.UID, d.tDate
    ) src
    PIVOT (
        SUM(sumValue) -- 对每个日期的sumValue求和
        FOR tDate IN (' + @cols + ')
    ) piv
    WHERE UID IS NOT NULL -- 过滤无用户数据的行
'

EXECUTE(@query)

-- 清理临时表
DROP TABLE #tmpDates

关键说明 #

1. 关联临时表：使用RIGHT JOIN确保所有指定日期都被包含，即使某个用户在该日期无数据
2. 动态列处理：
   • @cols用于Pivot子句中的列列表
   • @colsWithIsNull将Pivot后的NULL值替换为0，符合期望输出
3. 聚合逻辑：子查询中按UID和tDate分组，确保每个用户对应每个日期都有聚合值
4. 排序处理：生成动态列时按日期降序排列，匹配期望输出的列顺序

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