使用Linq的数据透视表

Question

我有一个表格，格式如下（VisitType是动态的）：

PersonelId        VisitDate       VisitTypeId
1                 2015-02-24      A
2                 2015-02-23      S
2                 2015-02-24      D
4                 2015-02-22      S
2                 2015-02-22      A
2                 2015-02-22      B
3                 2015-02-23      A
1                 2015-02-23      A
1                 2015-02-24      D
4                 2015-02-24      S
4                 2015-02-22      S
2                 2015-02-22      S
3                 2015-02-24      D

我希望使用linq获取以下数据的透视表。

VisitDate   PersonelId      A      S     D     B
2015-02-22  4               0      2     0     0
2015-02-22  2               1      1     0     0
2015-02-23  2               0      1     0     0
2015-02-23  3               1      0     0     0
2015-02-23  1               1      0     0     0
2015-02-24  1               1      0     1     0
2015-02-24  2               0      0     1     0
2015-02-24  4               0      1     0     0
2015-02-24  3               0      0     1     0

我使用这个linq

var d = (from f in _db.Visits
                 group f by new {f.VisitDate, f.PersonnelId }
                     into myGroup
                     where myGroup.Count() > 0
                     select new
                     {
                         myGroup.Key.VisitDate,
                         myGroup.Key.PersonnelId,
                         subject = myGroup.GroupBy(f => f.VisitTypeId).Select
                         (m => new { Sub = m.Count(), Score = m.Sum(c => c.Amount) })
                     }).ToList();

按日期和人员ID进行分组，但不要统计每个VisitType的项目。

- ar.gorgin

Answer 1

试试这个：

//static headers version
var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId})
    .Select(g=>new{
            VisitDate = g.Key.VisitDate,
            PersonelId = g.Key.PersonelId,
            A = g.Where(d=>d.VisitTypeId=="A").Count(),
            B = g.Where(d=>d.VisitTypeId=="B").Count(),
            D = g.Where(d=>d.VisitTypeId=="D").Count(),
            S = g.Where(d=>d.VisitTypeId=="S").Count()
            });

//dynamic headers version
var qry = Visits.GroupBy(v=>new{v.VisitDate, v.PersonelId})
    .Select(g=>new{
            VisitDate = g.Key.VisitDate,
            PersonelId = g.Key.PersonelId,
            subject = g.GroupBy(f => f.VisitTypeId)
                      .Select(m => new { Sub = m.Key, Score = m.Count()})
            });

- Maciej Los

---

谢谢，但VisitType是动态的（它是另一张表的外键）。 - ar.gorgin

你说“VisitType是动态的”是什么意思？ - Maciej Los

我有两个模型，Visit和VisitType。VisitType是Visit表中的外键。 - ar.gorgin

Answer 2

首先按照以下方式获取您的动态访问类型

var VisTyp= (from VT in _db.VisitType select new{VisType=VT.VisitType} ).ToList();

然后使用以下内容。

    var d = (from f in _db.Visits
     group f by new {f.VisitDate, f.PersonnelId } into myGroup
     where myGroup.Count() > 0
     select new
     {
           VisitDate = myGroup.Key.VisitDate,
           PersonnelId = myGroup.Key.PersonnelId,
           subject=myGroup.Count(t => VisTyp.Contains(t.VisitType))
     }).ToList();

- Dane