Pivot Table Using Linq

c#, group-by, linq, pivot, pivot-table

Solution

Try this:

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

Problem

I have a table like this .(VisitType is dynamic) ``` 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 ``` I want to get a pivot of this using linq as below. ``` 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 ``` I use this 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(); ``` It is grouped by date and personel id, but don't count items from every VisitType.

Original source