Forked from tejpaldev/Pivot C# Array or DataTable: Convert a Column To a Row with LINQ
Created
March 22, 2022 11:18
-
-
Save priyankakundu/247501588cdbde14f65144ebfdfbc8db to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Pivot C# Array or DataTable: Convert a Column To a Row with LINQ | |
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 } | |
}; | |
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); | |
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); | |
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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment