如何按列设置数据表行数据的格式

Question

我在c#中有一个数据表，其中包含按日期排列的员工信息。以下是数据表数据。

id   Name Salary  Hour     Date
---   ----- ------  -----   ------------------
1   Ram    10000.00 7.00    2018-11-01 00:00:00.000 1
1   Ram    10000.00 7.50    2018-11-02 00:00:00.000 2
2   Shyam   5000.00 8.50    2018-11-01 00:00:00.000 1
2   Shyam   5000.00 8.00    2018-11-02 00:00:00.000 2

I want the data table  result like below in c#
Name    2018-11-01  2018-11-02
------   ---------- ----------
Ram       7.00       7.50
Shyam     8.50       8.00
Can anybody help me on this?
Thanks

Answer 1

您可以尝试使用linqgroup by和MAX

var query = myList
.GroupBy(c => c.Name)
.Select(g => new {
Name = g.Key,
_2018_11_01 = g.Where(c => c.Date == new DateTime(2018, 11, 1)).Max(c => c.Hour),
_2018_11_02 = g.Where(c => c.Date == new DateTime(2018, 11, 2)).Max(c => c.Hour)
});

如果要动态生成列，可以尝试在方法中编写Lambda表达式。

public static class ExtetionClass {
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;
}
}

像这样使用

var pivotTable = myList.ToPivotTable(
item => item.Date,
item => item.Name,
items => items.Any() ? items.Max(x => x.Hour) : 0);
foreach (DataRow rw in pivotTable.Rows)
{
Console.WriteLine(string.Format("{0}   {1}   {2}", rw[0], rw[1], rw[2]));
}

c#在线

Ram   7.0   7.5
Shyam   8.50   8.0

参考链接

使用LINQ 将列转换为行