我有一张类似的桌子：
Match | Quarter | Profit
-------+---------+--------
1 | First | 10.00
1 | Second | 14.00
2 | First | 22.00
1 | Last | 11.00
2 | Second | 18.00
2 | Third | 16.00
2 | Last | 10.00
我想做到这一点：
Match | First Profit | Second Profit | Third Profit | Last Profit
-------+--------------+---------------+--------------+---------------
1 | 10.00 | 14.00 |0.00 |11.00
2 | 22.00 | 18.00 |16.00 |10.00
简单地说就是将行合并为一行。重要的是，如果一个季度没有发现值，则结果记录为0.00；
不确定如何使用交叉表函数来实现这一点？
我四处阅读，一直在努力寻找一个好的答案或解决方案。
提前谢谢你的帮助。
最佳答案
这应该可以做到：
SELECT Match
, sum(case when Quarter = 'First' then Profit else 0.00 end) as first_profit
, sum(case when Quarter = 'Second' then Profit else 0.00 end) as second_profit
, sum(case when Quarter = 'Third' then Profit else 0.00 end) as third_profit
, sum(case when Quarter = 'Last' then Profit else 0.00 end) as last_profit
FROM data
GROUP BY match
ORDER BY Match
下面是一个示例数据：
with data as (
select
1 as match, 'First' as Quarter, 10.00 as Profit
union
select
1 as match, 'Second' as Quarter, 14.00 as Profit
union
select
2 as match, 'First' as Quarter, 22.00 as Profit
union
select
1 as match, 'Last' as Quarter, 11.00 as Profit
union
select
2 as match, 'Second' as Quarter, 18.00 as Profit
union
select
2 as match, 'Third' as Quarter, 16.00 as Profit
union
select
2 as match, 'Last' as Quarter, 10.00 as Profit
)
SELECT Match
, sum(case when Quarter = 'First' then Profit else 0.00 end) as first_profit
, sum(case when Quarter = 'Second' then Profit else 0.00 end) as second_profit
, sum(case when Quarter = 'Third' then Profit else 0.00 end) as third_profit
, sum(case when Quarter = 'Last' then Profit else 0.00 end) as last_profit
FROM data
GROUP BY match
ORDER BY Match