给出以下结果集：
--------------------------------------------------------- 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.
谢谢你的帮助！
解决方法
这可以在没有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,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]
