-
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
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
- 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.
今天碰到一个需求，就是将下面表(1)格式的数据转换为表(2)格式的数据。很明显，这是一个行转列的要求，本想在数据库中行转列，因为在数据库中行转列是比较简单的，方法可以参考本站SQLServer中(行列转换)行转列及列转行且加平均值及汇总值，但因其它需求，最终需将该转化搬到C#中进行了。
客户名称日期金额
在将列转换为行之后，您可能希望获得List或dynamic[]，而不是获得DataTable。在这篇文章中，我们将做同样的事情，但是使用c#的数组和数据表，使用LINQ或Lambda表达式的力量。您可以使用相同的扩展方法来获取如下所示的行转列的 DataTable。为此，我更新了扩展方法以获得动态对象。C# 将Datatable行转列成Datatable。您可以为扩展方法创建一个静态类并将其放在那里。C# 将List集合行转列到匿名对象集合。现在，要将行转换为列并获得动态数组。
在数据处理的过程中，我们经常需要将表格中的列数据转化为行数据。这种操作在数据透视分析、数据清洗和报告生成中非常常见。在SQL Server中，UNPIVOT操作符为这种转换提供了一个简洁的解决方案。然而，在EF Core这样的ORM框架中，由于没有直接对应的操作符，我们需要使用LINQ进行手动处理。这篇文章将深入探讨如何在SQL Server中使用UNPIVOT以及如何在EF Core中实现同样的功能。将列数据转换为行数据是数据分析中常见的需求。
例子：班级 人数1班 10 2班 8 3班 9 4班 3 5班 6横向班级 1班 2班 3班 4班 5班人数 10 8 9 3 6 在datatable中进行行列的转置，所以先将您的数据都填充到datatable中。假设您的原始datatale是dt,在dt后加入下面的语句 DataTa
T-SQL语句中，Pivot运算符用于在列和行之间对数据进行旋转或透视转换，PIVOT命令可以实现数据表的列转行，同时执行聚合运算，UNPIVOT则与其相反，实现数据的行转列。PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。通俗简单的说：PIVOT就是...
撰写时间：2019、7、22
在做项目的过程中遇到过这样一个问题，我需要把数据库一张表的多条费用数据查出来，并且让他在一行显示，多条费用是指不同的费用，而我这个数据库表只有一个字段来储存这些费用，（这个字段储存的是外键，多种费用是固定在另一张表里，新增的时候只需要把外键新增到那个字段里），两个数据库表如下图所示：...
detail.[MasterId] ,
detail.[SamplingTime] ,
CASE WHEN detail.IsTest= 1 THEN detail.[Result] +'L' ELSE detail.[Result
/// <summary>
/// 将DataTable的第二列的值转化为列（即将原来的行表，转化成交叉表，没有对应值则默认0）贾世义
/// </summary>
/// <param name="dt">必须三列，第三列为值</param>
/// <returns></returns>
publi
目标在程序中实现行列转换效果 实现流程图 测试用的XML文件xml version="1.0" encoding="utf-8" ?>table> tr> 编码>A01编码> 内容>你内容> M0601>1M0601> M0602>2M0602> tr> tr> 编码>A02编
