Using pivot table in linq

Question

I have the following dynamic list

Crew NameSurname Period Result ABC John DOE Q1 54,09 ABC John DOE Q2 59,57 ABC John DOE Q3 62,11 

How to get this result in linq.

 Crew NameSurname Q1 Q2 Q3 ABC John DOE 47,51 47,51 51,46 

I tried this way, but I could not get the result

 List.GroupBy(c => c.PersonnelID) .Select(g => new { PersonnelID = g.Key, Period1 = g.Where(c => c.Period == 1).Sum(c => c.Result), Period2 = g.Where(c => c.Period == 2).Sum(c => c.Result), Period3 = g.Where(c => c.Period == 3).Sum(c => c.Result) }); 

Answer 1

You can do it:

 var results = Data.GroupBy(l => new { l.Crew, l.NameSurname}); .SelectMany( (key, g) => new { Crew = Key.Crew, NameSurname = Key.NameSurname, groups = g }); var pivoted = new List<PivotedCrew>(); foreach(var item in results) { pivoted.Add( new PivotedCrew { Crew = item.Crew, NameSurname = item.NameSurname, Q1 = item.groups.Where(x => x.Period == "Q1") .FirstOrDefault().value, Q2 = item.groups.Where(x => x.Period == "Q2") .FirstOrDefault().value, Q3 = item.groups.Where(x => x.Period == "Q3") .FirstOrDefault().value }); } 

But you will need to define a new class. Something like:

 public class PivotedCrew { public string Crew Id {get; set;} public string NameSurname {get; set;} public string Q1 {get; set;} public string Q2 {get; set;} public string Q3 {get; set;} }