/*
由CUBE 和 PIVOT 生成交叉行列汇总查询
1) 由CUBE求出各种组合的汇总值
2) 通过PIVOT进行行列转置
*/
USE AdventureWorks;
GO
SELECT SalesPerson,[2001],[2002],[2003],[2004],[Row Total]
FROM
(SELECT COALESCE(CAST(SalesPersonID AS NVARCHAR(10)),'Column Total') AS SalesPerson,
COALESCE(CAST(YEAR(QuotaDate) AS NCHAR(4)),'Row Total') AS [Year],
SUM(CAST(SalesQuota AS INT)) AS SumTotal,
GROUPING(SalesPersonID) AS PersonGrouping --Cube行标识
FROM Sales.SalesPersonQuotaHistory
GROUP BY SalesPersonID,Year(QuotaDate)
WITH CUBE) dv
PIVOT (SUM(SumTotal) FOR [Year]
IN ([2001],[2002],[2003],[2004],[Row Total])) pt
ORDER BY personGrouping,SalesPerson
--结果:
--SalesPerson 2001 2002 2003 2004 Row Total
--
--268 35000 455000 544000 271000 1305000
--275 923000 3805000 4716000 1718000 11162000
--... ... ... ... ... ...
--286 NULL 1002000 2940000 1615000 5557000
--287 NULL 321000 1481000 951000 2753000
--288 NULL NULL 172000 33000 205000
--289 NULL NULL 1294000 993000 2287000
--Column Total 9513000 29009000 38782000 18410000 95714000
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15042150/viewspace-620031/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15042150/viewspace-620031/