合并2行

Question

我有一个像这样的结果集。我想把支付的金额加起来，把数字电视拖车和FlatBED合并成1排。有什么办法我能做到吗？

CaseServiceID   PurchaseOrderID PaidAmount  DTV TOW FLATBED
227                 15000227    19.20           1   0
227                 15000227    45.00           0   1

注意：DTV TOW和FLATBED列来自一个旋转表

编辑

枢轴看起来是这样的：

select
*
from
(
    select
    cla.CaseServiceID
    ,cla.ServiceTypeProgKey
    ,cla.ClaimAmount
    ,cla.PaidAmount
    ,cla.ClaimQuantity
    from
    Claim cla
) as srcServiceType
pivot
(
    max(ServiceTypeProgKey) for ServiceTypeProgKey in ([FLATBED],[DTV TOW]
) as pvtServiceType

Answer 1

怎么样

SELECT CaseServiceID, 
       PurchaseOrderID, 
       SUM(PaidAmount) PaidAmount,
       SUM([DTV TOW]) [DTV TOW],
       SUM(FLATBED) FLATBED
FROM YourTable
GROUP BY CaseServiceID, PurchaseOrderID

Answer 2

查询最终是什么样子的。我希望这能帮上忙。谢谢

select
,sum(isnull([FLATBED], 0)) AS [FLATBED]
,sum(isnull([DTV TOW], 0)) AS [DTV TOW]
from
(
    select
    cla.ServiceTypeProgKey,
    ,sum(cla.PaidAmount) as 'PaidAmount'
    ,sum(cla.ClaimQuantity) as 'ClaimQuantity'
    ,sum(cla.PaidQuantity) as 'PaidQuantity'
    from
    Claim cla
    inner join CaseService cse on cla.CaseServiceID = cse.CaseServiceID
    group by cse.PurchaseOrderID
) as srcServiceType
pivot
(
     count(ServiceTypeProgKey) FOR ServiceTypeProgKey IN ([FLATBED],[DTV TOW])
) as pvtServiceType

...and结果

CaseServiceID   PurchaseOrderID   PaidAmount  DTV   TOW FLATBED
227             15000227          64.20       1     1