將總行添加到 PIVOT 查詢

我想在我的查詢中添加一個 TOTAL 月行，下面是我的 DDL，我怎樣才能在最後一個employeename標記的行下添加一個額外的行，TOTAL它顯示了該月所有員工SUM()的銷售額？

Create Table #empSales
(
   employeename varchar(100)
   ,saleamt decimal(10,2)
   ,saleMonth varchar(100)
)

Insert Into #empSales VALUES
('James', '1.00', 'January')
,('Richard', '3.28', 'January')
,('Barb', '4.13', 'January')


Select 
employeeName
,SUM(January) As JanAMt
,SUM(February) As FebAMt
,SUM(March) As MarAMt
,SUM(April) As AprAMt
,SUM(May) As MayAMt
,SUM(June) As JunAMt
,SUM(July) As JulAMt
,SUM(August) As AugAMt
,SUM(September) As SepAMt
,SUM(October) As OctAMt
,SUM(November) As NovAMt
,SUM(December) As DecAMt
FROM #empSales
PIVOT ( 
   SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December)
) As pvt
GROUP BY employeeName
Order By employeeName

可以將可選WITH ROLLUP子句添加到您GROUP BY的以獲得總計行。

GROUP BY employeeName WITH ROLLUP

要獲得“TOTAL”作為標籤，您可以使用GROUPING函式.

Select 
CASE WHEN GROUPING(employeename) = 1 THEN 'TOTAL' ELSE employeeName END

最終查詢如下所示：

Select 
CASE WHEN GROUPING(employeename) = 1 THEN 'TOTAL' ELSE employeeName END
,SUM(January) As JanAMt
,SUM(February) As FebAMt
,SUM(March) As MarAMt
,SUM(April) As AprAMt
,SUM(May) As MayAMt
,SUM(June) As JunAMt
,SUM(July) As JulAMt
,SUM(August) As AugAMt
,SUM(September) As SepAMt
,SUM(October) As OctAMt
,SUM(November) As NovAMt
,SUM(December) As DecAMt
FROM #empSales
PIVOT ( 
   SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December)
) As pvt
GROUP BY employeeName WITH ROLLUP
Order By GROUPING(employeename), employeeName

請注意，我調整了ORDER BY以使總行在結果集中排在最後。

引用自：https://dba.stackexchange.com/questions/270221