数据汇总透视表

Question

我有一个SQL临时表(#tmpProdData)，其中的数据格式如下：

TransAmount | CommAmount | Role1 | Role2 | ... | Role20
100.00      | 15.00      | 123   | 123   | ... | 456
200.00      | 60.00      | 123   | 456   | ... | 456
250.00      | 25.00      | 456   | 789   | ... | 789

Role1到Role20列有一个雇员ID (int)。我需要一个如下所示的结果表(#tmpEmpTotals)：

EmpID | Role1TransTotal | Role1CommTotal | Role2TransTotal | Role2CommTotal | ... | ... | Role20TransTotal | Role20CommTotal
123   | 300.00          | 75.00          | 100.00          | 15.00          | ... | ... | 0.00             | 0.00
456   | 250.00          | 25.00          | 200.00          | 60.00          | ... | ... | 300.00           | 75.00
789   | 0.00            | 0.00           | 250.00          | 25.00          | ... | ... | 250.00           | 25.00

任何角色中的每个员工都应该有一个条目，如果他们属于该角色，则具有SUM(TransAmount)和SUM(CommAmount)。因此，如果employee 123在#tmpProdData表中的Role1中，它将被添加到他的Role1TransTotal和Role1CommTotal的总数中。一名员工可以在同一记录中担任多个角色。

在示例中，您可以看到对于第一个和第二个记录，employee 123处于role1中，因此他的Role1CommTotal是记录1和2的CommAmount的总和。

我觉得PIVOT在这里会有帮助，但我似乎不能让它正常工作。我现在可以使用子选择SUM来做这件事，但我知道这是非常低效的。任何和所有的帮助都非常感谢！

Answer 1

嗯。。。是否要取消透视并重新聚合？

select v.empid,
       sum(case when v.rnum = 1 then t.commamount end) as role1commission,
       sum(case when v.rnum = 2 then t.commamount end) as role2commission,
       . .  
from #tmpProdData t cross apply
     (values (1, role1), (2, role2), . . . 
     ) v(rnum, empid)
where v.empid is not null
group by v.empid;