void Main()
{
List<Item> objItems = getSampleData();
var result = objItems.GroupBy(x => new { x.Project, x.State })
.Select(g =>
{
var a = g.ToList();
return
new
{
Project = a[0].Project,
State = a[0].State,
Count = g.Count()
};
});
var query = result
.GroupBy(c => c.State)
.Select(g => new {
ProjectName = g.Key,
Pending = g.Where(c => c.State == "Pending").Sum(c => c.Count),
Resolved = g.Where(c => c.State == "Resolved").Sum(c => c.Count),
Committed = g.Where(c => c.State == "Committed").Sum(c => c.Count),
Active = g.Where(c => c.State == "Active").Sum(c => c.Count)
});
}
class Item
{
public int Id { get; set; }
public string Project { get; set; }
public string Title { get; set; }
public string State { get; set; }
public Item (int id, string project, string title, string state)
{
this.Id = id;
this.Project = project;
this.Title = title;
this.State = state;
}
}
List<Item> getSampleData()
{
List<Item> objItems = new List<Item>();
objItems.Add(new Item(1, "ABC", "Title1", "Resolved" ));
objItems.Add(new Item(2, "ABC", "Title2", "Pending" ));
objItems.Add(new Item(3, "DEF", "Title3", "Archived" ));
objItems.Add(new Item(4, "DEF", "Title4", "Resolved" ));
objItems.Add(new Item(5, "DEF", "Title5", "Committed" ));
objItems.Add(new Item(6, "DEF", "Title6", "Active" ));
objItems.Add(new Item(7, "ABC", "Title2", "Pending" ));
return objItems;
}
the answer is not invented by me but taken from here
http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq[
^]