代码:Pivot.zip
数据透视表提供的数据三维视图效果,在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
|
|