SQL Server PIVOT - 多个聚合
给定以下结果集:
---------------------------------------------------------
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
Note that NetTotal = SubTotal - Tax
请帮我计算 sum(SubTotal)、sum(Tax) 和 sum(NetTotal),以及旋转的 TransType,如下:
Please help me calculate sum(SubTotal), sum(Tax) and sum(NetTotal), along with pivotted TransType, as follows:
--------------------------------------------------------------------------
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
--------------------------------------------------------------------------
如果我只有 1 列要汇总,那么使用 PIVOT 将是直接的,但我不确定如何获得 3 个聚合 - 用于 SubTotal、Tax 和 NetTotal.
If I had only 1 column to be summarized it would be straight forward using PIVOT, but I am not sure how to get the 3 aggregates - for SubTotal, Tax and NetTotal.
感谢您的帮助!
推荐答案
这可以在没有 PIVOT 的情况下完成:
This can be done without a 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,然后将这些查询与联接结合起来.下面的例子;为了使查询简单,我放弃了现金和支票.
With PIVOT, it gets considerably more complex. The easiest way I can think of is to calculate SubTotal, Tax, and NetTotal in a different query, and then combine the queries with a join. Example below; to keep the query simple, I've discarded Cash and Check.
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]
相关文章