Click here to Skip to main content
13,248,562 members (57,231 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have list of information with following fields:

Project    State         Title
  ABC      Resolved      Title1
  ABC      Pending       Title2
  DEF      Archived      Title3
  DEF      Resolved      Title4
  DEF      Committed     Title5
  DEF      Active        Title6

I want output in following format using c#
Project    Pending      Resolved       Committed     Active
  ABC         2            1              0            3
  DEF         1            3              1            15

I am using list of user defined class with following data members
public int Id { get; set; }
 
        public string Collection { get; set; }
 
        public string Project { get; set; }
 
        public string Title { get; set; }
 
        public string State { get; set; }



This is the best I could do :
var result = objItems.GroupBy(x => new { x.Project, x.State })
                .Select(g =>
                {
                    var a = g.ToList();
                    return
                    new
                    {
                        Project = a[0].Project,
                        Status = a[0].State,
                        Count = g.Count()
                    };
                });



Please help.

Thanks,
Posted 3-Jul-15 0:08am
Updated 4-Jul-15 7:23am
v3
Comments
   
What is the issue?
deepakdynamite 3-Jul-15 6:23am
   
I am not able to get desired output.. What I get is

Project name , State and count..

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

void Main()
{
	List<Item> objItems = getSampleData();
	// for linqpad objItems.Dump();
	
	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()
                    };
                });
											
   // for linqpad result.Dump();
   
   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)
    });
	
	// for linqpad query.Dump();
	
}
 
class Item
{
	public int Id { get; set; }
	//public string Collection { 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[^]
  Permalink  
Comments
Maciej Los 4-Jul-15 15:30pm
   
+5
Vitaliy Fedorchenko 19-Nov-15 2:55am
   
As alternative to writing custom grouping code you may use NReco PivotData library (free) that can do the same with 1-2 lines of code and provides universal approach for tasks like that (see my answer on StackOverflow linked above).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.171114.1 | Last Updated 4 Jul 2015
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100