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

By Brij Mohan

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.