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