Sep 8, 2015

Pivot C# Array or DataTable: Convert a Column To a Row with LINQ

My previous post explains how to convert a column to row in JavaScript array. In this post, we will do the same thing but with C# Array and DataTable using the power of LINQ or Lambda expression. For simplicity, I am using the same data.

C# Array To Pivot DataTable:

Here is the C# array object:

  var data = new[] { 
                new { Product = "Product 1", Year = 2009, Sales = 1212 },
                new { Product = "Product 2", Year = 2009, Sales = 522 },
                new { Product = "Product 1", Year = 2010, Sales = 1337 },
                new { Product = "Product 2", Year = 2011, Sales = 711 },
                new { Product = "Product 2", Year = 2012, Sales = 2245 },
                new { Product = "Product 3", Year = 2012, Sales = 1000 }
            };

On Googling, I found the following generic method in StackOverflow thread.

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

You can create a static class for extension methods and put it there.
To convert Year values to columns and get Pivot DataTable:

  var pivotTable = data.ToPivotTable(
                item => item.Year, 
                item => item.Product,  
                items => items.Any() ? items.Sum(x=>x.Sales) : 0);

You will get the following output:

C# Array to Pivot Dynamic Array:

You might want to get the List<dynamic> or dynamic[] instead of getting DataTable after converting columns to rows. It is handy in ASP.NET Web API to return JSON response.

To do it, I updated the extension method to get the dynamic object. use following extension method:

      public static dynamic[] ToPivotArray<T, TColumn, TRow, TData>(
 this IEnumerable<T> source,
 Func<T, TColumn> columnSelector,
 Expression<Func<T, TRow>> rowSelector,
 Func<IEnumerable<T>, TData> dataSelector)
        {

            var arr = new List<object>();
            var cols = new List<string>();
            String rowName = ((MemberExpression)rowSelector.Body).Member.Name;
            var columns = source.Select(columnSelector).Distinct();       

            cols =(new []{ rowName}).Concat(columns.Select(x=>x.ToString())).ToList();


            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))
                             }).ToArray();


            foreach (var row in rows)
            {
                var items = row.Values.Cast<object>().ToList();
                items.Insert(0, row.Key);
                var obj = GetAnonymousObject(cols, items);
                arr.Add(obj);               
            }
            return arr.ToArray();
        }
  private static dynamic GetAnonymousObject(IEnumerable<string> columns, IEnumerable<object> values)
        {
            IDictionary<string, object> eo = new ExpandoObject() as IDictionary<string, object>;
            int i;
            for (i = 0; i < columns.Count(); i++)
            {
                eo.Add(columns.ElementAt<string>(i), values.ElementAt<object>(i));
            }
            return eo;
        }

ExpandoObject is used to create dynamic object.
Now, to convert row to column and get dynamic array:

 var pivotArray = data.ToPivotArray(
                 item => item.Year,
                item => item.Product,
                items => items.Any() ? items.Sum(x => x.Sales) : 0);

You can easily convert in JSON format

 String json = JsonConvert.SerializeObject(pivotArray, new KeyValuePairConverter());

Here is the result:

C# DataTable to Pivot DataTable:

Let us have a DataTable with same data:

			DataTable myDataTable = new DataTable();
            myDataTable.Columns.AddRange(new DataColumn[3] { new DataColumn("Product"), new DataColumn("Year", typeof(int)), new DataColumn("Sales", typeof(int)) });
            myDataTable.Rows.Add("Product 1", 2009, 1212);
            myDataTable.Rows.Add("Product 2", 2009, 522);
            myDataTable.Rows.Add("Product 1", 2010, 1337);
            myDataTable.Rows.Add("Product 2", 2011, 711);
            myDataTable.Rows.Add("Product 2", 2012, 2245);
            myDataTable.Rows.Add("Product 3", 2012, 1000);   

You can use the same extension method to get Pivot DataTable like below.

 var data2 = myDataTable.AsEnumerable().Select(x=> new { 
                Product =x.Field<String>("Product"), 
                Year= x.Field<int>("Year"), 
                Sales = x.Field<int>("Sales") });
           
            DataTable pivotDataTable =data2.ToPivotTable(
                 item => item.Year,
                item => item.Product,
                items => items.Any() ? items.Sum(x => x.Sales) : 0);

Here is the result:

DataTable to List<dynamic>:

If you need to convert DataTable to List of dynamic object then use following extension method:

 public static List<dynamic> ToDynamicList(this DataTable dt)
        {
            var list = new List<dynamic>();
            foreach (DataRow row in dt.Rows)
            {
                dynamic dyn = new ExpandoObject();
                list.Add(dyn);
                foreach (DataColumn column in dt.Columns)
                {
                    var dic = (IDictionary<string, object>)dyn;
                    dic[column.ColumnName] = row[column];
                }
            }
            return list;
        }

Here is the result:

Conclusion:

In this post, we played with C# Array, DataTable and implemented to convert row to column and get Pivot Array, DataTable and List of dynamic object.

Hope, It helps.

12 comments

  1. How do you do this so each product has a row for each years sales, and the year also appears as part of the row header, like so:
    Product Names : J,F,M,A,M,J,J,A,S,O,N,D
    Product 1, 2017 : 5,4,2,5,4,1,2,5,6,3,2,5
    Product 2, 2017 : 1,5,8,9,5,2,1,0,1,0,1,5
    Product 2, 2018 : 5,2,5,1,2,5,8,4,0,0,0,2

    That’s the format you need to show the data in chart.js, labels before the colon and numbers after, so it’ll almost be an entire database to chart solution.

    1. Thank for this solution. In the dynamic array with multiple value a stucking point here. For the solution of multiple value

      public static dynamic[] ToPivotArray(
      this IEnumerable source,
      Func columnSelector,
      Expression<Func> rowSelector,
      Func<IEnumerable, TData> dataSelector)
      {

      var arr = new List();
      var cols = new List();
      var array = new List();
      // String rowName = ((MemberExpression)rowSelector.Body).Member.Name;
      var rowsName = ((NewExpression)rowSelector.Body).Members.Select(s => s).ToList();
      foreach (var row in rowsName)
      {
      var name = row.Name;
      array.Add(name);
      // table.Columns.Add(new DataColumn(name));
      }
      array.ToArray();
      var columns = source.Select(columnSelector).Distinct();

      // cols =(new []{ rowName}).Concat(columns.Select(x=>x.ToString())).ToList();

      cols =(array).Concat(columns.Select(x=>x.ToString())).ToList();

      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))
      }).ToArray();

      foreach (var row in rows)
      {
      var items = row.Values.Cast().ToList();

      string[] keyRow = row.Key.ToString().Split(‘,’);
      int index = 0;
      foreach (var key in keyRow)
      {
      string keyValue = key.Replace(“}”, “”).Split(‘=’)[1].Trim();
      items.Insert(index, keyValue);
      index++;
      }

      // items.Insert(0, row.Key);
      var obj = GetAnonymousObject(cols, items);
      arr.Add(obj);
      }
      return arr.ToArray();
      }

      private static dynamic GetAnonymousObject(IEnumerable columns, IEnumerable values)
      {
      IDictionary eo = new ExpandoObject() as IDictionary;
      int i;
      for (i = 0; i < columns.Count(); i++)
      {
      eo.Add(columns.ElementAt(i), values.ElementAt(i));
      }
      return eo;
      }
      var pivotArray = attendances.ToPivotArray(
      item => item.CurrentDate.Day,
      item =>new { item.ProjectId, item.ResourceId},
      items => items.Any() ? items.Sum(x => x.DailyEffort) : 0);

  2. Hi I want almost same thing but my problem is that the column which i am summing is a string column.See the question below

    I have a table like below

    name date hour Task
    sandeep 2018-11-01 5 coding
    sandeep 2018-11-01 3 design
    Swarup 2018-11-01 PTO PTO
    Swarup 2018-11-02 6 Scripting
    Swarup 2018-11-02 2 Specs

    I want the output like below

    Name 2018-11-01 2018-11-02 Task
    Sandeep 8 0 coding.design
    Swarup PTO 8 PTO,Scripting.Specs

    Can you please help me on this?. I am stuck on this code

  3. It is woking for me

    public static class ToPivot
    {
    public static DataTable ToPivotTable(
    this IEnumerable source,
    Func columnSelector,
    Expression<Func> rowSelector,
    Func<IEnumerable, TData> dataSelector)
    {
    DataTable table = new DataTable();

    var rowsName = ((NewExpression)rowSelector.Body).Members.Select(s => s).ToList();

    foreach (var row in rowsName)
    {
    var name = row as MemberInfo;

    table.Columns.Add(new DataColumn(name.Name));
    }

    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().ToList();

    string[] keyRow = row.Key.ToString().Split(‘,’);

    int index = 0;

    foreach (var key in keyRow)
    {
    string keyValue = key.Replace(“}”,””).Split(‘=’)[1].Trim();

    items.Insert(index, keyValue);

    index++;
    }

    dataRow.ItemArray = items.ToArray();
    table.Rows.Add(dataRow);
    }

    return table;
    }

    }

  4. This is a life saver code. I am extremely thankful for your effort. I wanted to know if you can help me with code where rowselector can have two fields.

    For eg

    var data1 = new[]{
    new{DeptLeader = “SCHWARTZ”, EmployeeName=”BENJAMIN”,Category=”Taxi”, UsdAmount= 14.0},
    new{DeptLeader = “SCHWARTZ”, EmployeeName=”BENJAMIN”,Category=”Rail”, UsdAmount= 314.0},
    new{DeptLeader = “SCHWARTZ”, EmployeeName=”BENJAMIN”,Category=”Rail”, UsdAmount= 114.0},
    new{DeptLeader = “SCHWARTZ”, EmployeeName=”BENJAMIN”,Category=”Parking”, UsdAmount= 44.0},

    new{DeptLeader = “SCHWARTZ”, EmployeeName=”MILLS”,Category=”Meals”, UsdAmount= 40.0},
    new{DeptLeader = “SCHWARTZ”, EmployeeName=”MILLS”,Category=”Taxi”, UsdAmount= 46.0},

    new{DeptLeader = “SCHWARTZ”, EmployeeName=”FOSTER”,Category=”Meals”, UsdAmount= 414.0},
    new{DeptLeader = “SCHWARTZ”, EmployeeName=”FOSTER”,Category=”Rate”, UsdAmount= 142.0},
    new{DeptLeader = “SCHWARTZ”, EmployeeName=”FOSTER”,Category=”Parking”, UsdAmount= 64.0},

    new{DeptLeader = “HENBEST”, EmployeeName=”HENBEST”,Category=”Meals”, UsdAmount= 40.0},
    new{DeptLeader = “HENBEST”, EmployeeName=”HENBEST”,Category=”Taxi”, UsdAmount= 46.0},

    new{DeptLeader = “HENBEST”, EmployeeName=”HENBEST”,Category=”Meals”, UsdAmount= 414.0},
    new{DeptLeader = “HENBEST”, EmployeeName=”HENBEST”,Category=”Rate”, UsdAmount= 142.0},
    new{DeptLeader = “HENBEST”, EmployeeName=”HENBEST”,Category=”Parking”, UsdAmount= 64.0},

    };

    var pivotArray1 = ToPivotArray(data1,
    item => item.Category,
    item => new { item.DeptLeader , item.EmployeeName },
    items => items.Any() ? items.Sum(x => x.UsdAmount) : 0);

  5. Sorry, what should I do, if I would like more columns grouping? Example an Employee name and his/her adress, hobby so second column employee1 name, third column address1, 4th column hobby1,5th column employee2 etc.
    I am not see where can I modify it. Grouping is just one Id

Leave a Reply

Your email address will not be published. Required fields are marked *