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

Accepted Answer

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

Solution

Problem

Related problems