在 linq 中使用数据透视表 [英] Using pivot table in linq

问题描述

我有以下动态列表

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

我怎样才能在 linq 中得到这个结果.

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

推荐答案

你可以这样做:

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

这篇关于在 linq 中使用数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！