在C＃中重组（移调）数据表 [英] Restructuring (transposing) datatable in c#

问题描述

我有类似下面的DataTable

I have a datatable like below

    TableName   RowId   columnname  ColumnValue
    A           1       C1          V1
    A           1       C2          V2
    A           2       C1          V3
    A           2       C2          V4

我要转以下结构

    TableName RowId C1 C2
    A         1     V1 V2
    A         2     V3 V4

如何做到在C＃？

How to do it in C#?

推荐答案

我prepared有点复杂的解决方案，这并不需要什么样的价值观在里面的ColumnName 隐藏源列数据表：

I prepared a little complex solution, which does not require what values are hidden inside ColumnName column of source DataTable:

源代码数据表 preparation：

Source DataTable preparation:

var source = new DataTable();
source.Columns.Add(new DataColumn("TableName", typeof(string)));
source.Columns.Add(new DataColumn("RowId", typeof(int)));
source.Columns.Add(new DataColumn("ColumnName", typeof(string)));
source.Columns.Add(new DataColumn("ColumnValue", typeof(string)));

source.Rows.Add("A", 1, "C1", "V1");
source.Rows.Add("A", 1, "C2", "V2");
source.Rows.Add("A", 2, "C1", "V3");
source.Rows.Add("A", 2, "C2", "V4");

目标数据表 preparation：

Target DataTable preparation:

var target = new DataTable();
target.Columns.Add(new DataColumn("TableName", typeof(string)));
target.Columns.Add(new DataColumn("RowId", typeof(int)));

帮助LINQ查询：

Helper LINQ query:

var query = from r in source.AsEnumerable()
            let i = new
            {
                TableName = r.Field<string>("TableName"),
                Id = r.Field<int>("RowId"),
                ColumnName = r.Field<string>("ColumnName"),
                ColumnValue = r.Field<string>("ColumnValue")
            }
            group i by new { i.TableName, i.Id } into g
            select g;

将数据插入目标 数据表：

foreach (var item in query)
{
    var newRow = target.NewRow();

    // static columns
    newRow["TableName"] = item.Key.TableName;
    newRow["RowId"] = item.Key.Id;

    // dynamic columns
    foreach (var c in item)
    {
        if(!target.Columns.Contains(c.ColumnName))
        {
            target.Columns.Add(new DataColumn(c.ColumnName, typeof(String)));
        }

        newRow[c.ColumnName] = c.ColumnValue;
    }

    target.Rows.Add(newRow);
}

这篇关于在C＃中重组（移调）数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！