Geek Answers Handbook

How to build a pivot table via linq using c #

I have this datatable in my C # code:

Date | Employee | Job1 | Job2 | Job3 | ---------|----------|------|------|-------| 1/1/2012 | A | 1.00 | 1 | 1 | 1/1/2012 | B | 2.5 | 2 | 2 | 1/1/2012 | C | 2.89 | 1 | 4 | 1/1/2012 | D | 4.11 | 2 | 1 | 1/2/2012 | A | 3 | 2 | 5 | 1/2/2012 | B | 2 | 2 | 2 | 1/2/2012 | C | 3 | 3 | 3 | 1/2/2012 | D | 1 | 1 | 1 | 1/3/2012 | A | 5 | 5 | 5 | 1/3/2012 | B | 2 | 2 | 6 | 1/3/2012 | C | 1 | 1 | 1 | 1/3/2012 | D | 2 | 3 | 4 | 2/1/2012 | A | 2 | 2 | 2 | 2/1/2012 | B | 5 | 5 | 2 | 2/1/2012 | D | 2 | 2 | 2 | 2/2/2012 | A | 3 | 3 | 3 | 2/2/2012 | B | 2 | 3 | 3 | 3/1/2012 | A | 4 | 4 | 2 |

Now I want to create another DataTable, which will look like this:

 Job1 Employee | 1/1/2012 | 1/2/2012 | 1/3/2012 | 2/1/2012 | 2/2/2012 | ---------|----------|----------|----------|----------|----------| A | 1.00 | 3 | 5 | 2 | 3 | B | 2.50 | 2 | 2 | 5 | 2 | C | 2.89 | 3 | 1 | - | | D | 4.11 | 1 | 2 | 2 | | Total | 10.50 | 9 | 10 | 9 | 5 |

Please suggest how to make this pivot table using Linq and C #.

+4
source share
2 answers
 var query = from foo in db.Foos group foo by foo.Date into g select new { Date = g.Key, A = g.Where(x => x.Employee == "A").Sum(x => x.Job1), B = g.Where(x => x.Employee == "B").Sum(x => x.Job1), C = g.Where(x => x.Employee == "C").Sum(x => x.Job1), D = g.Where(x => x.Employee == "D").Sum(x => x.Job1), Total = g.Sum(x => x.Job1) };

You can also apply the OrderBy(x => x.Date) query OrderBy(x => x.Date) .

+4
source

You cannot do this with LINQ due to the dynamic nature of the columns. LINQ to SQL requires a static way to map result set fields to property values. Instead, you can look into the PIVOT SQL statement and populate the results in a DataTable

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

+1
source

More articles:


All Articles