废话不多说,直接上代码,别问我代码是谁,为什么要上它?(注意,转发或者引用我的代码的,请标明出处连接,否则菊花难保!)
下面举两个列子的数据,如下代码所示
DataTable CreateDt1()
{
var dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("编号");
dt.Columns.Add("姓名");
dt.Columns.Add("类型");
dt.Columns.Add("值");
dt.Rows.Add(1,"0001","张三","身高","180");
dt.Rows.Add(2,"0001","张三","体重","120");
dt.Rows.Add(3,"0002","李四","身高","170");
dt.Rows.Add(4,"0002","李四","体重","130");
dt.Rows.Add(5,"0003","王五","身高","175");
dt.Rows.Add(6,"0003","王五","体重","135");
return dt;
}
DataTable CreateDt2(){
var dt= new DataTable();
dt.Columns.Add("户主编号");
dt.Columns.Add("户主姓名");
dt.Columns.Add("年月");
dt.Columns.Add("费用类型");
dt.Columns.Add("金额");
dt.Rows.Add("0001","张三","2019-6","电费",100);
dt.Rows.Add("0001","张三","2019-6","水费",90);
dt.Rows.Add("0001","张三","2019-6","燃气费",200);
dt.Rows.Add("0001","张三","2019-7","电费",110);
dt.Rows.Add("0001","张三","2019-7","水费",100);
dt.Rows.Add("0001","张三","2019-7","燃气费",190);
dt.Rows.Add("0002","李四","2019-6","电费",125);
dt.Rows.Add("0002","李四","2019-6","水费",100);
dt.Rows.Add("0002","李四","2019-6","燃气费",110);
dt.Rows.Add("0002","李四","2019-7","电费",120);
dt.Rows.Add("0002","李四","2019-7","水费",60);
dt.Rows.Add("0002","李四","2019-7","燃气费",150);
return dt;
}
数据1:
|
数据2:
|
行专列的通用方法:
/// <summary>
/// DataTable行转列
/// </summary>
/// <param name="source">数据源</param>
/// <param name="groupColumns">分组的列</param>
/// <param name="captionColumns">要作为列显示的列</param>
/// <param name="valueColumn">值列</param>
/// <returns></returns>
public static DataTable TableRowTurnToColumn(DataTable source, DataColumn[] groupColumns, DataColumn[] captionColumns, DataColumn valueColumn)
{
if (source == null)
return null;
DataTable dt = new DataTable();
if (groupColumns != null)
foreach (var item in groupColumns)
{
dt.Columns.Add(item.ColumnName, item.DataType);
}
foreach (var item in source.AsEnumerable().Select(p => string.Join("_", captionColumns.Select(q => p[q]))).Distinct())
{
if (!dt.Columns.Contains(item.ToString()))
dt.Columns.Add(item.ToString());
}
var groupdata = source.AsEnumerable().GroupBy(p => string.Join("", groupColumns.Select(q => p[q]))).ToList();
var groupColNames = groupColumns.Select(p => p.ColumnName);
groupdata.ForEach(x =>
{
DataRow newRow = dt.NewRow();
foreach (DataColumn col in dt.Columns)
{
if (groupColNames.Contains(col.ColumnName))
{
newRow[col.ColumnName] = x.FirstOrDefault()[col.ColumnName];
}
else
{
var data = x.Where(p => string.Join("_", captionColumns.Select(q => p[q])) == col.ColumnName);
if (data.Count() > 0)
{
var numList = data.Where(p => decimal.TryParse(p[valueColumn].ToString(), out decimal v));//取数值类型的数据,若有就加总,若不是数值数据则用逗号拼接
if (numList.Count() > 0)
{
newRow[col.ColumnName] = numList.Sum(p => Convert.ToDecimal(p[valueColumn]));
}
else
{
newRow[col.ColumnName] = string.Join(",", data.Select(p => p[valueColumn]));//如果值不是数值类型的处理
}
}
}
}
dt.Rows.Add(newRow);
});
return dt;
}
方法调用:
var dt1=CreateDt1();
var dt2=CreateDt2();
var dt11=TableRowTurnToColumn(dt1,new DataColumn[]{dt1.Columns[1],dt1.Columns[2]},new DataColumn[]{dt1.Columns[3]},dt1.Columns[4]);
var dt21=TableRowTurnToColumn(dt2,new DataColumn[]{dt2.Columns[0],dt2.Columns[1]},new DataColumn[]{dt2.Columns[2],dt2.Columns[3]},dt2.Columns[4]);
打印结果:
数据1行转列的结果:
|
数据2行转列的结果:
|
合在一起的直观结果:
注:若要多个值,如下图的TeamQuantity 和FuelQuantity的两个值的,转成第三个表的结果,可以私聊。