如何将列表对象转换为数据透视表 [英] How do I convert list object into pivot

问题描述

我有以下字段的信息列表：

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

我想要输出以下格式使用c＃

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,

推荐答案

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

答案不是我发明的，而是从这里开始的



http：/ /stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq [ ^ ]

这篇关于如何将列表对象转换为数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！