将列表转换为数据透视表 [英] Convert List to a Pivot List

问题描述

我有一个清单:

IList<PIData> list = new List<PIData>()

这会返回一个像这样的列表:

This returns a list like this:

Timestamp        | End              | HeaderTitle | Value
=========================================================
12/12/2012 00:00 | 12/12/2012 00:01 | Test1       | 0.23
12/12/2012 00:00 | 12/12/2012 00:01 | Test2       | 0.34
12/12/2012 00:00 | 12/12/2012 00:01 | Test3       | 0.556

这种情况一直持续下去，有时我会有 50-100 个不同的 HeaderTitles

This continues on and on where sometimes I will have 50-100 different HeaderTitles

我需要能够对此进行旋转并最终将其写入 CSV，其中 Row 是标题.我知道如何将对象转换为 CSV，但我很难旋转列表并希望有人能提供帮助.

I need to be able to pivot this and ultimately write it to a CSV with Row being a header. I know how to convert an object to CSV but I am having a super hard time pivoting the list and hoping someone can help.

这是我想要的样子:

Timestamp        | End              | Test1 | Test2 | Test3 | etc
==================================================================
12/12/2012 00:00 | 12/12/2012 00:01 | 0.23  | 0.34  | 0.556
12/12/2012 00:01 | 12/12/2012 00:02 | 0.23  | 0.34  | 0.556
12/12/2012 00:02 | 12/12/2012 00:03 | 0.23  | 0.34  | 0.556

有人可以帮我完成这项工作吗?我真的只需要能够将我的列表转换为一个最终将成为 CSV 文件的新列表...我知道如何在 SQL 中执行此操作，但在这种情况下我无法使用 SQL.

Can someone help me make this work? I really just need to be able to pivot my List to a new List that will ultimately be a CSV file... I know how to do it in SQL but I cannot use SQL in this scenario.

推荐答案

这是我最终做的对我有用的事情:

Here is what I ended up doing what worked for me:

namespace ConsoleApplication3
{
class Program
{

    internal class PiData
    {
        public string HeaderName { get; set; }
        public DateTime TimeStamp { get; set; }
        public DateTime End { get; set; }
        public double Value { get; set; }
    }

    static void Main(string[] args)
    {
        var PD = new List<PiData>()
        {
            new PiData() { HeaderName="Test1", End = DateTime.Now.AddSeconds(15), TimeStamp = DateTime.Now, Value = 0.01 },
            new PiData() { HeaderName="Testf2", End = DateTime.Now.AddSeconds(15), TimeStamp = DateTime.Now, Value = 0.51 },
            new PiData() { HeaderName="Testff3", End = DateTime.Now.AddSeconds(15), TimeStamp = DateTime.Now, Value = 0.71 },
            new PiData() { HeaderName="Testfsd4", End = DateTime.Now.AddSeconds(15), TimeStamp = DateTime.Now, Value = 0.41 },
            new PiData() { HeaderName="Test1", End = DateTime.Now.AddSeconds(30), TimeStamp = DateTime.Now.AddSeconds(15), Value = 0.01 },
            new PiData() { HeaderName="Testf2", End = DateTime.Now.AddSeconds(30), TimeStamp = DateTime.Now.AddSeconds(15), Value = 0.51 },
            new PiData() { HeaderName="Testff3", End = DateTime.Now.AddSeconds(30), TimeStamp = DateTime.Now.AddSeconds(15), Value = 0.71 },
            new PiData() { HeaderName="Testfsd4", End = DateTime.Now.AddSeconds(30), TimeStamp = DateTime.Now.AddSeconds(15), Value = 0.41 },
        };


    var result2 = PD.Pivot(emp => emp.TimeStamp, emp2 => emp2.HeaderName, lst => lst.Sum(a => a.Value));
    StringBuilder sb = new StringBuilder();

    List<string> columns = new List<string>();
    columns.Add("TimeStamp");
    columns.Add("End");
    foreach (var item in PD.Select(a => a.HeaderName).Distinct())
    {
        columns.Add(item);
    }
    foreach (var item in columns)
    {
        sb.Append(item + ",");
    }
    sb.Remove(sb.Length - 1, 1);
    sb.AppendLine();
    foreach (var row in result2)
    {
        sb.Append(row.Key + "," + row.Key.AddSeconds(10).ToString() + ",");
        foreach (var column in row.Value)
        {
            sb.Append(column.Value + ",");
        }
        sb.Remove(sb.Length - 1, 1);
        sb.AppendLine();
    }
    Console.WriteLine(sb.ToString());
    Console.WriteLine("----"); 

    }
}

public static class LinqExtenions
{

    public static Dictionary<TFirstKey, Dictionary<TSecondKey, TValue>> Pivot<TSource, TFirstKey, TSecondKey, TValue>(this IEnumerable<TSource> source, Func<TSource, TFirstKey> firstKeySelector, Func<TSource, TSecondKey> secondKeySelector, Func<IEnumerable<TSource>, TValue> aggregate)
    {
        var retVal = new Dictionary<TFirstKey, Dictionary<TSecondKey, TValue>>();

        var l = source.ToLookup(firstKeySelector);
        foreach (var item in l)
        {
            var dict = new Dictionary<TSecondKey, TValue>();
            retVal.Add(item.Key, dict);
            var subdict = item.ToLookup(secondKeySelector);
            foreach (var subitem in subdict)
            {
                dict.Add(subitem.Key, aggregate(subitem));
            }
        }

        return retVal;
    }
}


}

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