Restructuring (transposing) datatable in c#

Question

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

I want to transpose to below structure

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

How to do it in C#?

See http://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C — Cameron Tinker, Aug 16 '13 at 13:11
You can do a group by on the data and create another datatable with restructured data. Get the enumerable for data table data and group by TableName,RowId,ColumnName . Related transpose question: http://stackoverflow.com/questions/9958821/transpose-in-datatable-in-asp-net?rq=1 — Rohith Nair, Aug 16 '13 at 13:19

MarcinJuraszek · Accepted Answer · 2013-08-16T13:31:45.200

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

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");

Target DataTable preparation:

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

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;

Inserting data into target DataTable:

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);
}

Restructuring (transposing) datatable in c#

1 Answers1

Linked