Sunday, September 10, 2017

Pivot DataTable Using LINQ

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()

0 comments:

Post a Comment