How to change the data layout of a data table with LINQ

Question

Above is the screen shot of one of my Data Table. I am trying to transform this data into the following format so that I can bind it to one of my grid. I have tried LINQ but unsuccessful. Could please anyone help me how I can do this. Doesn't necessarily be LINQ but I think it will be easier with LINQ

Answer 1

try below

var result =  dataSet.Tables["reportColumns"].AsEnumerable().GroupBy(x => x.Field<string>("Object"))
    .Select(g => new
    {
        ColumnName = g.Key,
        DefaultColumn = g.FirstOrDefault(p => p.Field<string>("Attribute") == "DefaultColumn").Field<string>("Value"),
        Label = g.FirstOrDefault(p => p.Field<string>("Attribute") == "Label").Field<string>("Value"),
        Type = g.FirstOrDefault(p => p.Field<string>("Attribute") == "Type").Field<string>("Value"),
        Standard = g.FirstOrDefault().Field<int>("Standard")
    }).ToList();

Answer 2

You can use my ToPivotTable extension:

public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
      this IEnumerable<T> source,
      Func<T, TColumn> columnSelector,
      Expression<Func<T, TRow>> rowSelector,
      Func<IEnumerable<T>, TData> dataSelector)
{
    DataTable table = new DataTable();
    var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
    table.Columns.Add(new DataColumn(rowName));
    var columns = source.Select(columnSelector).Distinct();

    foreach (var column in columns)
        table.Columns.Add(new DataColumn(column.ToString()));

    var rows = source.GroupBy(rowSelector.Compile())
                     .Select(rowGroup => new
                     {
                         Key = rowGroup.Key,
                         Values = columns.GroupJoin(
                             rowGroup,
                             c => c,
                             r => columnSelector(r),
                             (c, columnGroup) => dataSelector(columnGroup))
                     });

    foreach (var row in rows)
    {
        var dataRow = table.NewRow();
        var items = row.Values.Cast<object>().ToList();
        items.Insert(0, row.Key);
        dataRow.ItemArray = items.ToArray();
        table.Rows.Add(dataRow);
    }

    return table;
}

Create strongly-typed data from your source table:

var data = from r in table.AsEnumerable()
           select new {
                Object = r.Field<string>("Object"),
                Attribute = r.Field<string>("Attribute"),
                Value = r.Field<object>("Value")
           };

And convert them to pivot table:

var pivotTable = data.ToPivotTable(r => r.Attribute, 
                                   r => r.Object, 
                                   rows => rows.First().Value);

This will create pivot table with distinct values of Attribute (i.e. DefaultColumn, Label, Type) as columns, rows will be groups for each Object value, and each cell will have value of corresponding Value property for object group and attribute column.

---

Or in single query:

var pivotTable = table.AsEnumerable()
                      .Select(r => new {
                          Object = r.Field<string>("Object"),
                          Attribute = r.Field<string>("Attribute"),
                          Value = r.Field<object>("Value")
                      })
                      .ToPivotTable(r => r.Attribute, 
                                    r => r.Object, 
                                    rows => rows.First().Value);