Donate

Pivot DataTable Using LINQ In C# And VB.NET

Hello,

A question was brought up in the forums on how to Pivot a DataTable object here.The OP has already a solution with reference to this link Cross Tab / Pivot from Data Table. An alternative solution is to utilize the features of LINQ using group by statement to achieve the desired output. This solution consists of few lines of code compared with the solution from the forum post.
C# Code
 var query = (from students in dt.AsEnumerable()
  group students by students.Field<string>("StudID") into g
  select new
  {
   StudID = g.Key,
   Eng = g.Where(c => c.Field<string>("SubSht") == "Eng").Sum(c => c.Field<double>("Score")),
   Fre = g.Where(c => c.Field<string>("SubSht") == "Fre").Sum(c => c.Field<double>("Score")),
   Mat = g.Where(c => c.Field<string>("SubSht") == "Mat").Sum(c => c.Field<double>("Score")),
  }).ToList();

VB.NET Code
Dim query = (From students In dt.AsEnumerable
     Group students By ID = students.Field(Of String)("StudID") Into g = Group
     Select New With {
         Key ID,
         .Eng = g.Where(Function(c) c.Field(Of String)("SubSht") = "Eng").Sum(Function(c) c.Field(Of Double)("Score")), _
         .Fre = g.Where(Function(c) c.Field(Of String)("SubSht") = "Fre").Sum(Function(c) c.Field(Of Double)("Score")), _
         .Mat = g.Where(Function(c) c.Field(Of String)("SubSht") = "Mat").Sum(Function(c) c.Field(Of Double)("Score"))
     }).OrderBy(Function(tkey) tkey.ID).ToList()



Cheers!

Comments

Donate

Popular Posts From This Blog

WPF CRUD Application Using DataGrid, MVVM Pattern, Entity Framework, And C#.NET

How To Insert Or Add Emojis In Microsoft Teams Status Message

Bootstrap Modal In ASP.NET MVC With CRUD Operations