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 can I get this result with in linq.

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

I've tried this way but I couldn't 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)
     });

L2S is an ORM (sort-of), not a replacement for SQL. If you want to do complex SQL transformations like PIVOT, it's far easier and more performant to write the SQL statement and use your ORM to map the results to objects. — Panagiotis Kanavos, Dec 28 '12 at 08:31
Similar SO question http://stackoverflow.com/questions/963491/pivot-data-using-linq?rq=1 — Amitd, Dec 28 '12 at 08:32

Mahmoud Gamal · Accepted Answer · 2012-12-28T09:09:01.857

You can do this:

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;}   
}

Using pivot table in linq

1 Answers1

Linked