我有一个带有agentID、transDate 和transValue 的table。我如何报告每个代理、年份和月份的所有 transValue？这些列应该如下所示： agentID|year|January|February... 在每个月下必须对 transValue 进行汇总。现在我有一部分代码：
select agentID, year(transDate), sum(transValue) as January
from operations
where month(transDate) = 01
group by agentID, year(transDate)我不知道如何添加接下来的几个月。有什么建议么？
回答1
您可以使用条件聚合来生成该报告 -
select agentID, year(transDate),
sum(CASE WHEN month(transDate) = 1 THEN transValue END) as January,
sum(CASE WHEN month(transDate) = 2 THEN transValue END) as february,
sum(CASE WHEN month(transDate) = 3 THEN transValue END) as March,
sum(CASE WHEN month(transDate) = 4 THEN transValue END) as April,
sum(CASE WHEN month(transDate) = 5 THEN transValue END) as May,
sum(CASE WHEN month(transDate) = 6 THEN transValue END) as June,
sum(CASE WHEN month(transDate) = 7 THEN transValue END) as July,
sum(CASE WHEN month(transDate) = 8 THEN transValue END) as August,
sum(CASE WHEN month(transDate) = 9 THEN transValue END) as Spetember,
sum(CASE WHEN month(transDate) = 10 THEN transValue END) as October,
sum(CASE WHEN month(transDate) = 11 THEN transValue END) as November,
sum(CASE WHEN month(transDate) = 12 THEN transValue END) as December
from operations
group by agentID, year(transDate)