[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.
這邊我們要展示如何使用LINQ實作Pivot功能，共兩個方式，首先我們需要一個示範的資料來源。


That table with two brand(both having two products)in four months will be use in this demo.
這個有兩個品牌(各有兩個產品)在四個月間的表格將會用在這次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.
要用LINQ做出同樣的pivot表格，我們可以使用ToPivotTable這個從stackoverflow討論串來的擴充方法，這擴充方法會回傳DataTable為結果，印到view後長的這樣。


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.
就我所知ToPivotTable擴充方法是沒辦法辦到的，所以我們要換個方法。

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。