给定以下结果集:

---------------------------------------------------------
CustomerID  Service  TransType  SubTotal   Tax   NetTotal
---------------------------------------------------------
106            A        CREDIT     12.52     -      12.52
106            A        CREDIT     10.07     -      10.07
106            B        CREDIT      2.00     -       2.00
106            C        REMOTE      5.99     -       5.99
106            C        CREDIT      5.99     -       5.99
106            C        CREDIT      3.99  0.30       3.69
106            C        CREDIT      5.99  0.30       5.69
106            D        CREDIT      5.99     -       5.99
---------------------------------------------------------


请注意,NetTotal =小计-税

请帮我计算总和(SubTotal),总和(Tax)和总和(NetTotal),以及透视的TransType,如下所示:

--------------------------------------------------------------------------
CustomerID  Service  Cash  Check  Credit  Remote  SubTotal   Tax  NetTotal
--------------------------------------------------------------------------
106            A        0      0   22.59       0     22.59     0     22.59   
106            B        0      0    2.00       0      2.00     0      2.00    
106            C        0      0   15.97    5.99     21.96  0.60     21.36    
106            D        0      0    5.99       0      5.99     0      5.99    
--------------------------------------------------------------------------


如果我仅要总结一列,那么使用PIVOT会很简单,但是我不确定如何获取这三项汇总-小计,税和NetTotal。

谢谢你的帮助!

最佳答案

这可以在没有PIVOT的情况下完成:

SELECT
  CustomerID
, [Service]
, Cash = SUM(case when TransType='CASH' then SubTotal else 0 end)
, [Check] = SUM(case when TransType='CHECK' then SubTotal else 0 end)
, Credit = SUM(case when TransType='CREDIT' then SubTotal else 0 end)
, [Remote] = SUM(case when TransType='REMOTE' then SubTotal else 0 end)
, SubTotal = SUM(SubTotal)
, Tax = SUM(Tax)
, NetTotal = SUM(NetTotal)
FROM YourTable
GROUP BY CustomerId, [Service]


使用PIVOT,它变得相当复杂。我想到的最简单的方法是在另一个查询中计算SubTotal,Tax和NetTotal,然后将查询与联接合并。下面的例子;为了简化查询,我舍弃了现金和支票。

SELECT
  a.CustomerId
, a.Service
, Credit = a.Credit
, [Remote] = a.[Remote]
, SubTotal = SUM(b.SubTotal)
, Tax = SUM(b.Tax)
, NetTotal = SUM(b.NetTotal)
FROM (
    SELECT
      CustomerId
    , [Service]
    , Credit = SUM(Credit)
    , [Remote] = SUM([Remote])
    FROM YourTable a
    PIVOT
    (
        SUM(SubTotal) FOR [TransType] IN ([Credit],[Remote])
    ) pvt
    GROUP BY CustomerId, [Service]
) a
INNER JOIN YourTable b
    ON a.CustomerID = b.CustomerID
    AND a.[Service] = b.[Service]
GROUP BY a.CustomerId, a.[Service], a.Credit, a.[Remote]

10-05 19:27