使用PIVOT汇总数据：SQL临时表角色金额统计需求

嘿，我来帮你搞定这个SQL统计的问题！你之前用子查询效率低是因为要反复扫描原表，而PIVOT没成功大概率是没先做UNPIVOT的步骤——毕竟你要先把多列的角色转成行，才能正确聚合再转回去。下面是一个高效的解决方案，只需要扫原表一次，性能会比子查询好很多：

核心思路 #

1. UNPIVOT（宽表转窄表）：把Role1到Role20这20列转成行，让每个员工-角色组合对应一条交易/佣金记录
2. 分组聚合：按员工ID和角色分组，计算每个员工在对应角色下的交易总额和佣金总额
3. PIVOT（窄表转宽表）：把聚合后的结果转回目标的宽表结构，用ISNULL将空值替换为0，满足“未担任角色金额为0”的要求

完整SQL代码 #

-- 第一步：将多角色列转成行，关联对应的交易与佣金数据
WITH UnpivotedData AS (
    SELECT 
        TransAmount,
        CommAmount,
        RoleNumber, -- 标记当前是Role1/Role2...Role20
        EmpID
    FROM #tmpProdData
    UNPIVOT (
        EmpID FOR RoleNumber IN (Role1, Role2, Role3, Role4, Role5, 
                                 Role6, Role7, Role8, Role9, Role10,
                                 Role11, Role12, Role13, Role14, Role15,
                                 Role16, Role17, Role18, Role19, Role20)
    ) AS up
),
-- 第二步：按员工+角色分组，计算总额
AggregatedData AS (
    SELECT 
        EmpID,
        RoleNumber,
        SUM(TransAmount) AS TransTotal,
        SUM(CommAmount) AS CommTotal
    FROM UnpivotedData
    GROUP BY EmpID, RoleNumber
)
-- 第三步：转回目标宽表结构，空值填充为0
SELECT 
    EmpID,
    ISNULL(Role1TransTotal, 0.00) AS Role1TransTotal,
    ISNULL(Role1CommTotal, 0.00) AS Role1CommTotal,
    ISNULL(Role2TransTotal, 0.00) AS Role2TransTotal,
    ISNULL(Role2CommTotal, 0.00) AS Role2CommTotal,
    ISNULL(Role3TransTotal, 0.00) AS Role3TransTotal,
    ISNULL(Role3CommTotal, 0.00) AS Role3CommTotal,
    ISNULL(Role4TransTotal, 0.00) AS Role4TransTotal,
    ISNULL(Role4CommTotal, 0.00) AS Role4CommTotal,
    ISNULL(Role5TransTotal, 0.00) AS Role5TransTotal,
    ISNULL(Role5CommTotal, 0.00) AS Role5CommTotal,
    ISNULL(Role6TransTotal, 0.00) AS Role6TransTotal,
    ISNULL(Role6CommTotal, 0.00) AS Role6CommTotal,
    ISNULL(Role7TransTotal, 0.00) AS Role7TransTotal,
    ISNULL(Role7CommTotal, 0.00) AS Role7CommTotal,
    ISNULL(Role8TransTotal, 0.00) AS Role8TransTotal,
    ISNULL(Role8CommTotal, 0.00) AS Role8CommTotal,
    ISNULL(Role9TransTotal, 0.00) AS Role9TransTotal,
    ISNULL(Role9CommTotal, 0.00) AS Role9CommTotal,
    ISNULL(Role10TransTotal, 0.00) AS Role10TransTotal,
    ISNULL(Role10CommTotal, 0.00) AS Role10CommTotal,
    ISNULL(Role11TransTotal, 0.00) AS Role11TransTotal,
    ISNULL(Role11CommTotal, 0.00) AS Role11CommTotal,
    ISNULL(Role12TransTotal, 0.00) AS Role12TransTotal,
    ISNULL(Role12CommTotal, 0.00) AS Role12CommTotal,
    ISNULL(Role13TransTotal, 0.00) AS Role13TransTotal,
    ISNULL(Role13CommTotal, 0.00) AS Role13CommTotal,
    ISNULL(Role14TransTotal, 0.00) AS Role14TransTotal,
    ISNULL(Role14CommTotal, 0.00) AS Role14CommTotal,
    ISNULL(Role15TransTotal, 0.00) AS Role15TransTotal,
    ISNULL(Role15CommTotal, 0.00) AS Role15CommTotal,
    ISNULL(Role16TransTotal, 0.00) AS Role16TransTotal,
    ISNULL(Role16CommTotal, 0.00) AS Role16CommTotal,
    ISNULL(Role17TransTotal, 0.00) AS Role17TransTotal,
    ISNULL(Role17CommTotal, 0.00) AS Role17CommTotal,
    ISNULL(Role18TransTotal, 0.00) AS Role18TransTotal,
    ISNULL(Role18CommTotal, 0.00) AS Role18CommTotal,
    ISNULL(Role19TransTotal, 0.00) AS Role19TransTotal,
    ISNULL(Role19CommTotal, 0.00) AS Role19CommTotal,
    ISNULL(Role20TransTotal, 0.00) AS Role20TransTotal,
    ISNULL(Role20CommTotal, 0.00) AS Role20CommTotal
INTO #tmpEmpTotals -- 生成目标临时表
FROM AggregatedData
PIVOT (
    SUM(TransTotal) FOR RoleNumber IN (Role1, Role2, Role3, Role4, Role5, 
                                       Role6, Role7, Role8, Role9, Role10,
                                       Role11, Role12, Role13, Role14, Role15,
                                       Role16, Role17, Role18, Role19, Role20)
) AS pvtTrans
PIVOT (
    SUM(CommTotal) FOR RoleNumber IN (Role1, Role2, Role3, Role4, Role5, 
                                       Role6, Role7, Role8, Role9, Role10,
                                       Role11, Role12, Role13, Role14, Role15,
                                       Role16, Role17, Role18, Role19, Role20)
) AS pvtComm
ORDER BY EmpID;

为什么这个方案高效？ #

• 仅扫描原表一次：UNPIVOT阶段只需要读取一次#tmpProdData，而子查询方案需要为每个Role列单独扫描一次表（20次扫描），性能差距明显
• 聚合逻辑简洁：窄表分组聚合的计算效率远高于多列子查询求和
• PIVOT仅做结构转换：最后一步的PIVOT只是重新排列数据结构，几乎没有额外性能开销

可选替代方案（更灵活） #

如果你的SQL Server版本支持，也可以用CROSS APPLY结合VALUES替代UNPIVOT，写法更直观，方便后续扩展（比如过滤特定角色）：

WITH UnpivotedData AS (
    SELECT 
        TransAmount,
        CommAmount,
        RoleNumber,
        EmpID
    FROM #tmpProdData
    CROSS APPLY (
        VALUES 
            ('Role1', Role1),
            ('Role2', Role2),
            ('Role3', Role3),
            -- ... 依次添加到Role20
            ('Role20', Role20)
    ) AS ca(RoleNumber, EmpID)
)
-- 后续聚合和PIVOT逻辑和上面一致

内容的提问来源于stack exchange，提问作者D.R.