sql-server – SQL Server PIVOT – 多个聚合

---------------------------------------------------------
CustomerIDServiceTransTypeSubTotal TaxNetTotal
---------------------------------------------------------
106  A CREDIT12.52   -    12.52 
106            A CREDIT10.07   -    10.07
106  B CREDIT2.00   -    2.00
106  C REMOTE5.99   -    5.99
106  C CREDIT5.99   -    5.99
106  C CREDIT3.990.30    3.69
106  C CREDIT5.990.30    5.69
106  D CREDIT5.99   -    5.99
---------------------------------------------------------

注意NetTotal = SubTotal – Tax

请帮助我计算总额(SubTotal),总和(税)和总和(NetTotal)以及枢轴TransType,如下所示：

--------------------------------------------------------------------------
CustomerIDServiceCashCheckCreditRemoteSubTotal TaxNetTotal
--------------------------------------------------------------------------
106   A0 022.59 0   22.59 0 22.59
106   B0 02.000   2.00 0 2.00
106   C0 015.975.99   21.96 0.60 21.36
106   D0 05.990   5.990 5.99
--------------------------------------------------------------------------

如果我只列出1列可以使用PIVOT直接进行,但我不知道如何获得3个聚合 – SubTotal,Tax和NetTotal.

谢谢你的帮助！

解决方法

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,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]

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!