如何在C中透视列表#

如果你事先知道你有多少道具,你可以这样做

var query = myList
    .GroupBy(c => c.TimeStamp)
    .Select(g => new {
        TimeStamp = g.Key,
        Prop1 = g.Where(x => x.Name == "Prop1").Sum(x => x.Value),
        Prop2 = g.Where(x => x.Name == "Prop2").Sum(x => x.Value),
        Prop3 = g.Where(x => x.Name == "Prop3").Sum(x => x.Value),
        Value = g.Sum(c => c.Value)
    });

如果这个道具列表是动态的,则必须在服务器上调用pivot并手动查询它。或者使用子列表和groupby名称

我将在这个问题上加上5美分。我已经实现了一个简单的pivot表类,它完全满足您的需要。

 public static class PivotTable
    {
        public static PivotTable<TRow, TColumn, TValue> Create<TRow, TColumn, TValue>(Dictionary<TRow, Dictionary<TColumn, TValue>> dictionary)
        where TRow : IComparable, IEquatable<TRow>
        where TColumn : IComparable, IEquatable<TColumn>
        {
            return new PivotTable<TRow, TColumn, TValue>(dictionary);
        }
    }
    public class PivotTable<TRow, TColumn, TValue>
        where TRow : IComparable, IEquatable<TRow>
        where TColumn : IComparable, IEquatable<TColumn>
    {
        private readonly Dictionary<TRow, Dictionary<TColumn, TValue>> _dictionary;

        public PivotTable(Dictionary<TRow, Dictionary<TColumn, TValue>> dictionary)
        {
            _dictionary = dictionary;
        }

        public bool HasValue(TRow row, TColumn col)
        {
            return _dictionary.ContainsKey(row) && _dictionary[row].ContainsKey(col);
        }

        public TValue GetValue(TRow row, TColumn col)
        {
            return _dictionary[row][col];
        }

        public string Print()
        {
            var separator = " ";
            var padRight = 15;

            var rows = _dictionary.Keys;
            var columns = _dictionary.SelectMany(x => x.Value.Keys).Distinct().OrderBy(x => x).ToList();

            var sb = new StringBuilder();

            var columnsRow = new[] { "" }.ToList();

            columnsRow.AddRange(columns.Select(x => x.ToString()));

            sb.AppendLine(string.Join(separator, columnsRow.Select(x => x.PadRight(padRight))));

            foreach (var row in rows.OrderBy(x => x))
            {
                sb.Append(row.ToString().PadRight(padRight)).Append(" ");

                foreach (var col in columns.OrderBy(x => x))
                {
                    var val = HasValue(row, col) ? GetValue(row, col).ToString() : default(TValue).ToString();
                    sb.Append(val.PadRight(padRight)).Append(" ");
                }
                sb.AppendLine();
            }

            return sb.ToString();
        }
}

        public class Element
        {
            public DateTime TimeStamp { get; set; }
            public string Name { get; set; }
            public int Value { get; set; }
        }

 public static class Extensions
    {
        public static PivotTable<TRow, TColumn, TValue> ToPivot<TItem, TRow, TColumn, TValue>(
            this IEnumerable<TItem> source,
            Func<TItem, TRow> rowSelector,
            Func<TItem, TColumn> colSelector,
            Func<IEnumerable<TItem>, TValue> aggregatFunc
        )
            where TRow : IComparable, IEquatable<TRow>
            where TColumn : IComparable, IEquatable<TColumn>
        {
            var dic = source
                .GroupBy(rowSelector)
                .ToDictionary(x => x.Key, x => x.GroupBy(colSelector).ToDictionary(y => y.Key, y => aggregatFunc(y)));

            return PivotTable.Create(dic);
        }

然后可以对实现IEnumerable的任何类型调用它。您可以选择如何聚合元素。它还支持漂亮的打印,按行/列获取元素。

In your case you need to do this:

 var elements = new List<Element>();
            //fill in with some data
 var pivot = elements.ToPivot(x => x.TimeStamp, x => x.Name, x => x.Sum(y => y.Value));

核心功能就是这个。

var dic = source
                .GroupBy(rowSelector)
                .ToDictionary(x => x.Key, x => x.GroupBy(colSelector).ToDictionary(y => y.Key, y => aggregatFunc(y)));

如果不需要其他功能,请执行以下操作:

 var dic = elements //Dictionary<DateTime,Dictionary<string,int>>
                    .GroupBy(x=>x.TimeStamp)
                    .ToDictionary(x => x.Key, x => x.GroupBy(x=>x.Name).ToDictionary(y => y.Key, y => y=>y.Sum(z=>z.Value));

var date = new DateTime(2018,6,6);
var prop = "Prop1";

dic[date][prop] // will give you a sum for this particular date and prop

 class Program
    {
        static void Main(string[] args)
        {
            var elements = new Element[]
            {
                new Element
                {
                    TimeStamp = new DateTime(2018,5,1),
                    Name = "Prop1",
                    Value = 5,
                },
                 new Element
                {
                    TimeStamp = new DateTime(2018,5,7),
                    Name = "Prop1",
                    Value = 9,
                },
                  new Element
                {
                    TimeStamp = new DateTime(2018,5,1),
                    Name = "Prop2",
                    Value = 8,
                },
                   new Element
                {
                    TimeStamp = new DateTime(2018,5,7),
                    Name = "Prop2",
                    Value = 11,
                },
                    new Element
                {
                    TimeStamp = new DateTime(2018,5,1),
                    Name = "Prop3",
                    Value = 18,
                },
                     new Element
                {
                    TimeStamp = new DateTime(2018,5,7),
                    Name = "Prop3",
                    Value = 18,
                },
            };            
            var pivot = from line in elements
            group line by line.TimeStamp into g
            select new { g.Key, Props=g.Select(el=>new { el.Name, el.Value }).ToArray(), Total = g.Sum(line => line.Value) };
            int propCount = pivot.Max(line => line.Props.Count());
            string[] props = pivot.SelectMany(p => p.Props, (parent, c) => c.Name).Distinct().ToArray();
            Console.Write($"Date\t");
            for (int i = 0; i < propCount; i++)
            {
                Console.Write($"{props[i]}\t");
            }
            Console.Write($"Total");
            Console.WriteLine();

            foreach (var pivotLine in pivot)
            {
                Console.Write($"{pivotLine.Key.ToShortDateString()}\t");
                for (int i = 0; i < propCount; i++)
                {
                    Console.Write($"{pivotLine.Props.FirstOrDefault(p=>p.Name==props[i])?.Value}\t");
                }
                Console.Write($"{pivotLine.Total}\t");
                Console.WriteLine();
            }
        }

        class Element
        {
            public DateTime TimeStamp { get; set; }
            public string Name { get; set; }

            public int Value;
        }