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.

9 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.

  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 *