Wednesday, June 6, 2018

[C#] Implement Pivot with LINQ | 用 LINQ 實現 Pivot

Here we will demo how to using LINQ implement Pivot feature in two ways, at first we need a demo data source.

That table with two brand(both having two products)in four months will be use in this demo.

SQL Pivot

If we need a table shows sum of each brand in every months, using SQL pivot implement will look like this.
如果我們需要一個顯示品牌每個月的總計的表格,用SQL pivot來實現的話長得像下面這樣。

Easy but the month columns was fixed (or you can declare a column string first then put in here), not so flex.
簡單,但是月份欄位是固定的 (或是可以先生成一個欄位字串丟進來),不是很靈活。

ToPivotTable Extension

To using LINQ to get same pivot table, we can use ToPivotTable extension from stackoverflow thread "How to pivot data in LINQ without hard coding columns", the extension return result as DataTable, print on view like this.

Easy and flex, but only can handle one key, if you need both brand and product in this case, like this.
簡單且靈活,但只能用在一個key值的時候,在這個例子中,如果想同時要 brand 跟 product,像是這樣。
As I know, ToPivotTable extension can't do it, so we need to change another way.

LINQ Pivot

To get custom column, for example brand/product and sum column, we have to create pivot table by ourselves, you can check code at here, the result look like this.
要做到自訂欄位,比如說brand/product跟sum欄位,我們必須自己產生 pivot 表格,你可以在這邊查看範例程式,其結果如下。

Because we create pivot table by ourselves, so we can fully control all data on it, if need complex table, this way will be a better choose.
因為我們是自己建pivot table,所以我們可以完全控制所有資料,如果需要複雜的表格,這方法會是比較好的選擇。

I had upload demo project to github, you can check controller for pivot demo and sql script for data source.
我已經把demo專案傳到github了,要看pivot demo可以看controller,測試資料可以看sql script

No comments:

Post a Comment