linq C＃MVC中的动态旋转 [英] Dynamic pivoting in linq C# MVC

问题描述

我的linq列表中有以下数据。

I have below data in my linq list.

StatusID Count MonthYear 
======== ===== =========
   1      0     Jan 2014
   2      1     Feb 2013
   1      2     Jan 2013
   3      1     Dec 2014
   2      0     Nov 2014
   5      6     Jun 2015

现在我的要求是我需要以下列表格式。其中MonthYear列数据未修复。它可以是任何月份和年份数据。

Now my requirement is i need above list in below format. Where MonthYear column data is not fix. It can be any month and year data.

StatusID  Jan 2013  Feb 2013  Jan 2014 Nov 2014 Dec 2014 Jun 2015
========  ========  ========  ======== ======== ======== ========
   1        2          0         0       0        0        0
   2        0          1         0       0        0        0
   3        0          0         0       0        1        0
   5        0          0         0       0        0        6

我在stackoverflow上阅读了很多解决方案，甚至尝试了我自己的方式，但我没有成功。下面是我上次尝试的代码。



我尝试过的事情：

I read lots of solution on stackoverflow and even tried my own way but i was not get success. Below is code which i tried last.

What I have tried:

var pvtData = new PivotData(new[] { "StatusID", "MonthYear" }, new SumAggregatorFactory("count"));
            pvtData.ProcessData(finalList, (o, f) =>
            {
                var custData = (MontlyChartModified)o;
                switch (f)
                {
                    case "StatusID": return custData.StatusID;
                    case "MonthYear":
                        return custData.MonthYear;
                    case "count": return custData.count;
                }
                return null;
            });

推荐答案

请看一个例​​子：

Please, see an example:

void Main()
{

	List<Visit> Visits = new List<Visit>
	{
		new Visit(1, new DateTime(2015,2,24), "A"),
		new Visit(2, new DateTime(2015,2,23), "S"),
		new Visit(2, new DateTime(2015,2,24), "D"),
		new Visit(4, new DateTime(2015,2,22), "S"),
		new Visit(2, new DateTime(2015,2,22), "A"),
		new Visit(2, new DateTime(2015,2,22), "B"),
		new Visit(3, new DateTime(2015,2,23), "A"),
		new Visit(1, new DateTime(2015,2,23), "A"),
		new Visit(1, new DateTime(2015,2,24), "D"),
		new Visit(4, new DateTime(2015,2,24), "S"),
		new Visit(4, new DateTime(2015,2,22), "S"),
		new Visit(2, new DateTime(2015,2,24), "S"),
		new Visit(3, new DateTime(2015,2,24), "D")
	};

	//static headers
	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
	var qry1 = 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()})
				});
}

// class definition
public class Visit
{
	private int id = 0;
	private DateTime vd;
	private string vt = string.Empty;
	
	public Visit(int _id, DateTime _vd, string _vt)
	{
		id = _id;
		vd = _vd;
		vt = _vt;
	}
	
	public int PersonelId
	{
		get{return id;}
		set{id = value;}
	}
	
	public DateTime VisitDate
		{
		get{return vd;}
		set{vd = value;}
	}

	public string VisitTypeId
	{
		get{return vt;}
		set{vt = value;}
	}
}

这篇关于linq C＃MVC中的动态旋转的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！