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.
Good morning, how can I run that example or maybe they had a demo of the exercise.
I am getting error on topivottable object while calling the methods
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.
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);
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
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;
}
}
Thank you this is exactly what I needed.
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);
please you can help me y have the same problem y have three rowselector what i can do?
Did this get answered?
I have the same problem.
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
Did this get answered?