使用动态列数聚合数据表 [英] Aggregate datatable with dynamic number of columns

问题描述

我有一个包含动态列集的数据表，并希望聚合基于数字的列并将最终行保留到新数据表中.

I have a datatable with dynamic set of columns and want to aggregate the numeric based columns and keep the final rows into new datatable.

数据表示例:-

PartnerName   CreditCol  DebitCol  AmountCol ....
P1              10       20        30 
P2              1        2         3 
P3              3        1         10
P2              1        100       200

所需的输出应该是:-

PartnerName   CreditCol  DebitCol  AmountCol ....
P1              10       20        30 
P2              2        102       203 
P3              3        1         10

这里的主要内容是列集，将是动态的.有时，可能有两列，有时可能是 20 列.请建议 linq 查询或任何其他解决方案.

The main thing here is the column set and will be dynamic. Sometime, there could be two columns and sometimes it could be 20 cols. Please suggest the linq query or any other solution.

推荐答案

这是一个动态的方法，应该可以满足您的要求:

Here is a dynamic approach that should work for your requirement:

var rows = table.AsEnumerable();
var columns = table.Columns.Cast<DataColumn>();
int i;  // used to check if a string column can be parsed to int
string columnToGroup = "partnername";
DataColumn colToGroup = columns.First(c => c.ColumnName.Equals(columnToGroup, StringComparison.OrdinalIgnoreCase));
var colsToSum = columns
     .Where(c => c != colToGroup &&
         (c.DataType == typeof(int) ||
         (c.DataType == typeof(string)
         && rows.All(r => int.TryParse(r.Field<string>(c), out i)))));
var columnsToSum = new HashSet<DataColumn>(colsToSum);

DataTable tblSum = table.Clone(); // empty table, same schema
foreach (var group in rows.GroupBy(r => r[colToGroup]))
{
    DataRow row = tblSum.Rows.Add();
    foreach(var col in columns)
    {
        if (columnsToSum.Contains(col))
        {
            int sum;
            if (col.DataType == typeof(int))
                sum = group.Sum(r => r.Field<int>(col));
            else
                sum = group.Sum(r => int.Parse(r.Field<string>(col)));
            row.SetField(col.ColumnName, sum);
        }
        else
            row[col.ColumnName] = group.First()[col];
    }
}

在此处使用您的示例数据进行测试:

Tested with your sample data here:

var table = new System.Data.DataTable();
table.Columns.Add("PartnerName", typeof(string));
table.Columns.Add("CreditCol", typeof(int));
table.Columns.Add("DebitCol", typeof(string));
table.Columns.Add("AmountCol", typeof(int));
table.Rows.Add("P1", 10, "20", 30);
table.Rows.Add("P2", 1, "2", 3);
table.Rows.Add("P3", 3, "1", 10);
table.Rows.Add("P2", 1, "100", 200);

结果:

PartnerName   CreditCol    DebitCol    AmountCol
P1            10             20         30
P2            2              102        203
P3            3              1          10

这篇关于使用动态列数聚合数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！