C# DataTable 行转列通用方法

刺骨的言语ヽ痛彻心扉 2023-06-26 05:40 37阅读 0赞

废话不多说，直接上代码，别问我代码是谁，为什么要上它？（注意，转发或者引用我的代码的，请标明出处连接，否则菊花难保！）

下面举两个列子的数据，如下代码所示

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：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <table> 
     <tbody> 
      <tr> 
       <td colspan="5"><a rel="nofollow">Result Set (6 items)</a><a rel="nofollow">4</a></td> 
      </tr> 
      <tr> 
       <th>id</th> 
       <th>编号</th> 
       <th>姓名</th> 
       <th>类型</th> 
       <th>值</th> 
      </tr> 
      <tr> 
       <td>1</td> 
       <td>0001</td> 
       <td>张三</td> 
       <td>身高</td> 
       <td>180</td> 
      </tr> 
      <tr> 
       <td>2</td> 
       <td>0001</td> 
       <td>张三</td> 
       <td>体重</td> 
       <td>120</td> 
      </tr> 
      <tr> 
       <td>3</td> 
       <td>0002</td> 
       <td>李四</td> 
       <td>身高</td> 
       <td>170</td> 
      </tr> 
      <tr> 
       <td>4</td> 
       <td>0002</td> 
       <td>李四</td> 
       <td>体重</td> 
       <td>130</td> 
      </tr> 
      <tr> 
       <td>5</td> 
       <td>0003</td> 
       <td>王五</td> 
       <td>身高</td> 
       <td>175</td> 
      </tr> 
      <tr> 
       <td>6</td> 
       <td>0003</td> 
       <td>王五</td> 
       <td>体重</td> 
       <td>135</td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
 </tbody> 
</table>

数据2：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <table> 
     <tbody> 
      <tr> 
       <td colspan="5"><a rel="nofollow">Result Set (12 items)</a><a rel="nofollow">4</a></td> 
      </tr> 
      <tr> 
       <th>户主编号</th> 
       <th>户主姓名</th> 
       <th>年月</th> 
       <th>费用类型</th> 
       <th>金额</th> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>2019-6</td> 
       <td>电费</td> 
       <td>100</td> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>2019-6</td> 
       <td>水费</td> 
       <td>90</td> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>2019-6</td> 
       <td>燃气费</td> 
       <td>200</td> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>2019-7</td> 
       <td>电费</td> 
       <td>110</td> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>2019-7</td> 
       <td>水费</td> 
       <td>100</td> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>2019-7</td> 
       <td>燃气费</td> 
       <td>190</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>2019-6</td> 
       <td>电费</td> 
       <td>125</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>2019-6</td> 
       <td>水费</td> 
       <td>100</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>2019-6</td> 
       <td>燃气费</td> 
       <td>110</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>2019-7</td> 
       <td>电费</td> 
       <td>120</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>2019-7</td> 
       <td>水费</td> 
       <td>60</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>2019-7</td> 
       <td>燃气费</td> 
       <td>150</td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
 </tbody> 
</table>

行专列的通用方法：

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){
    					newRow[col.ColumnName]=data.Sum(p=>Convert.ToDecimal(p[valueColumn]));
    					}
                        }
                    }
                    dt.Rows.Add(newRow);   
                });
                return dt;
            }

方法调用：

打印结果：

数据1行转列的结果：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <table> 
     <tbody> 
      <tr> 
       <td colspan="4"><a rel="nofollow">Result Set (3 items)</a><a rel="nofollow">4</a></td> 
      </tr> 
      <tr> 
       <th>编号</th> 
       <th>姓名</th> 
       <th>身高</th> 
       <th>体重</th> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>180</td> 
       <td>120</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>170</td> 
       <td>130</td> 
      </tr> 
      <tr> 
       <td>0003</td> 
       <td>王五</td> 
       <td>175</td> 
       <td>135</td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
 </tbody> 
</table>

数据2行转列的结果：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <table> 
     <tbody> 
      <tr> 
       <td colspan="8"><a rel="nofollow">Result Set (2 items)</a><a rel="nofollow">4</a></td> 
      </tr> 
      <tr> 
       <th>户主编号</th> 
       <th>户主姓名</th> 
       <th>2019-6_电费</th> 
       <th>2019-6_水费</th> 
       <th>2019-6_燃气费</th> 
       <th>2019-7_电费</th> 
       <th>2019-7_水费</th> 
       <th>2019-7_燃气费</th> 
      </tr> 
      <tr> 
       <td>0001</td> 
       <td>张三</td> 
       <td>100</td> 
       <td>90</td> 
       <td>200</td> 
       <td>110</td> 
       <td>100</td> 
       <td>190</td> 
      </tr> 
      <tr> 
       <td>0002</td> 
       <td>李四</td> 
       <td>125</td> 
       <td>100</td> 
       <td>110</td> 
       <td>120</td> 
       <td>60</td> 
       <td>150</td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
 </tbody> 
</table>

合在一起的直观结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTc1OTA_size_16_color_FFFFFF_t_70][]

注：若要多个值，如下图的TeamQuantity 和FuelQuantity的两个值的，转成第三个表的结果，可以私聊。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTc1OTA_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTc1OTA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191230144947794.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTc1OTA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTc1OTA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200102140504820.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIwOTc1OTA=,size_16,color_FFFFFF,t_70