当前位置: 动力学知识库 > 问答 > 编程问答 >

Is it possible to Pivot data using LINQ?

问题描述:

I am new to LINQ, but I am wondering if it is possible to use LINQ to pivot data from the following layout:

CustID | OrderDate | Qty

1 | 1/1/2008 | 100

2 | 1/2/2008 | 200

1 | 2/2/2008 | 350

2 | 2/28/2008 | 221

1 | 3/12/2008 | 250

2 | 3/15/2008 | 2150

into something like this:

CustID | Jan- 2008 | Feb- 2008 | Mar - 2008 |

1 | 100 | 350 | 250

2 | 200 | 221 | 2150

网友答案:

Something like this?

List<CustData> myList = GetCustData();

var query = myList
    .GroupBy(c => c.CustId)
    .Select(g => new {
        CustId = g.Key,
        Jan = g.Where(c => c.OrderDate.Month == 1).Sum(c => c.Qty),
        Feb = g.Where(c => c.OrderDate.Month == 2).Sum(c => c.Qty),
        March = g.Where(c => c.OrderDate.Month == 3).Sum(c => c.Qty)
    });

GroupBy in Linq does not work the same as SQL. In SQL, you get the key and aggregates (row/column shape). In Linq, you get the key and any elements as children of the key (hierarchical shape). To pivot, you must project the hierarchy back into a row/column form of your choosing.

网友答案:

I answered similar question using linq extension method:

// order s(ource) by OrderDate to have proper column ordering
var r = s.Pivot3(e => e.custID, e => e.OrderDate.ToString("MMM-yyyy")
    , lst => lst.Sum(e => e.Qty));
// order r(esult) by CustID

(+) generic implementation
(-) definitely slower than David B's

Can anyone improve my implementation (i.e. the method does the ordering of columns & rows)?

网友答案:

Here is a bit more generic way how to pivot data using LINQ:

IEnumerable<CustData> s;
var groupedData = s.ToLookup( 
        k => new ValueKey(
            k.CustID, // 1st dimension
            String.Format("{0}-{1}", k.OrderDate.Month, k.OrderDate.Year // 2nd dimension
        ) ) );
var rowKeys = groupedData.Select(g => (int)g.Key.DimKeys[0]).Distinct().OrderBy(k=>k);
var columnKeys = groupedData.Select(g => (string)g.Key.DimKeys[1]).Distinct().OrderBy(k=>k);
foreach (var row in rowKeys) {
    Console.Write("CustID {0}: ", row);
    foreach (var column in columnKeys) {
        Console.Write("{0:####} ", groupedData[new ValueKey(row,column)].Sum(r=>r.Qty) );
    }
    Console.WriteLine();
}

where ValueKey is a special class that represents multidimensional key:

public sealed class ValueKey {
    public readonly object[] DimKeys;
    public ValueKey(params object[] dimKeys) {
        DimKeys = dimKeys;
    }
    public override int GetHashCode() {
        if (DimKeys==null) return 0;
        int hashCode = DimKeys.Length;
        for (int i = 0; i < DimKeys.Length; i++) { 
            hashCode ^= DimKeys[i].GetHashCode();
        }
        return hashCode;
    }
    public override bool Equals(object obj) {
        if ( obj==null || !(obj is ValueKey))
            return false;
        var x = DimKeys;
        var y = ((ValueKey)obj).DimKeys;
        if (ReferenceEquals(x,y))
            return true;
        if (x.Length!=y.Length)
            return false;
        for (int i = 0; i < x.Length; i++) {
            if (!x[i].Equals(y[i]))
                return false;
        }
        return true;            
    }
}

This approach can be used for grouping by N-dimensions (n>2) and will work fine for rather small datasets. For large datasets (up to 1 mln of records and more) or for cases when pivot configuration cannot be hardcoded I've written special PivotData library (it is free):

var pvtData = new PivotData(new []{"CustID","OrderDate"}, new SumAggregatorFactory("Qty"));
pvtData.ProcessData(s, (o, f) => {
    var custData = (TT)o;
    switch (f) {
        case "CustID": return custData.CustID;
        case "OrderDate": 
        return String.Format("{0}-{1}", custData.OrderDate.Month, custData.OrderDate.Year);
        case "Qty": return custData.Qty;
    }
    return null;
} );
Console.WriteLine( pvtData[1, "1-2008"].Value );  
网友答案:

Group your data on month, and then project it into a new datatable with columns for each month. The new table would be your pivot table.

分享给朋友:
您可能感兴趣的文章:
随机阅读: