sql - 将行转换为列并使用 group by 求和

Question

我有一个带有列的表ID，WorkingDate其中AmountReceived数据显示为

ID    WorkingDate     AmountReceived
-------------------------------------
1     13/April/2021   201999.01 
2     14/Arpil/2021     1099.02

现在，我试图展示它

13/April/2021      14/April/2021    15/April/2021      
--------------------------------------------------
201999.01          1099.02          102.09

我试过这个

select AmountReceived, WorkingDate
from Orders o

select distinct o.WorkingDate, sum(AmountReceived) over (partition by WorkingDate) as total 
from Orders o
group by WorkingDate, AmountReceived

但它会引发聚合错误。

标签： sqlsql-servertsqlsql-server-2008stored-procedures

Answer 1

使用PIVOT运算符，您可以实现您想要的

 select *
 from
 (
    select o.WorkingDate, o.AmountReceived
    from   Orders o
 ) o
 pivot
 (
     sum(AmountReceived)
     for WorkingDate in ([2021-04-13], [2021-04-14], [2021-04-15])
 ) p