数据透视表提供的数据三维视图效果,在Microsoft Excel能创建数据透视表,但是,它并不会总是很 方便使用Excel。您可能希望在Web应用程序中创建一个数据透视报表。创建一个简单的数据透视表可能是 一件非常复杂的任务。所以,我打算不但为你提供一个非常有用的工具创建简单和高级的数据透视表,而 且为你移除一些笼罩他们的神秘面纱。
目标是:我们想要有能力将datatable中的二维的数据转换成三维视图。
在大多数情况下,你会从数据库的查询数据填充数据表,例如
SELECt
SalesPeople.FullName AS [Sales Person]
, Products.FullName AS [Product]
, SUM(Sales.SalesAmount) AS [Sale Amount]
, SUM(Sales.Qty) AS [Quantity]
FROM
Sales
JOIN
SalesPeople WITH (NOLOCK)
ON SalesPeople.SalesPersonID = Sales.SalesPersonID
JOIN
Products WITH (NOLOCK)
ON Products.ProductCode = Sales.ProductCode
GROUP BY
SalesPeople.FullName
, Products.FullName
该查询会产生下面的数据表:
Sales Person | Product | Quantity | Sale Amount |
John | Pens | 200 | 350 |
John | Pencils | 400 | 500 |
John | Notebooks | 100 | 300 |
John | Rulers | 50 | 100 |
John | Calculators | 120 | 1200 |
John | Back Packs | 75 | 1500 |
Jane | Pens | 225 | 393.75 |
Jane | Pencils | 335 | 418.75 |
Jane | Notebooks | 200 | 600 |
Jane | Rulers | 75 | 150 |
Jane | Calculators | 80 | 800 |
Jane | Back Packs | 97 | 1940 |
Sally | Pens | 202 | 353.5 |
Sally | Pencils | 303 | 378.75 |
Sally | Notebooks | 198 | 600 |
Sally | Rulers | 98 | 594 |
Sally | Calculators | 80 | 800 |
Sally | Back Packs | 101 | 2020 |
Sarah | Pens | 112 | 196 |
Sarah | Pencils | 245 | 306.25 |
Sarah | Notebooks | 198 | 594 |
Sarah | Rulers | 50 | 100 |
Sarah | Calculators | 66 | 660 |
Sarah | Back Packs | 50 | 2020 |
时间: 2024-08-03 09:57:42