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

C# Source Code: How To Pivot DataTable Using LINQ In C#.NET

VB.NET Source Code: How To Pivot DataTable Using LINQ In VB.NET

Cheers!