Is it possible to Pivot data using LINQ?

Question

I am new to LINQ, but I am wondering if it is possible to use LINQ to pivot data from the following layout:

CustID | OrderDate | Qty
1      | 1/1/2008  | 100
2      | 1/2/2008  | 200
1      | 2/2/2008  | 350
2      | 2/28/2008 | 221
1      | 3/12/2008 | 250
2      | 3/15/2008 | 2150

into something like this:

CustID  | Jan- 2008 | Feb- 2008 | Mar - 2008 |
1       | 100       | 350       |  250
2       | 200       | 221       | 2150

Answer 1

What you can do for this is to group your data on month, and then project it into a new datatable with column s for each month. The new table would be your pivot table.

I will write up some code in a bit.

edit: well i came back to start to write something up but it looks like some people posted something.

Answer 2

There is a post on the MSDN forums on how to accomplish this. Basically, you need to push your data into a data table and then loop over the table to push it into a new, pivoted, datatable. MSDN

Answer 3

Something like this?

        List<CustData> myList = GetCustData();

        var query = myList
            .GroupBy(c => c.CustId)
            .Select(g => new {
                CustId = g.Key,
                Jan = g.Where(c => c.OrderDate.Month == 1).Sum(c => c.Qty),
                Feb = g.Where(c => c.OrderDate.Month == 2).Sum(c => c.Qty),
                March = g.Where(c => c.OrderDate.Month == 3).Sum(c => c.Qty)
            });

GroupBy in Linq does not work the same as SQL. In SQL, you get the key and aggregates (row/column shape). In Linq, you get the key and any elements as children of the key (hierarchical shape). To pivot, you must project the hierarchy back into a row/column form of your choosing.