Linq转换或透视行到列

本文关键字：透视 转换 Linq | 更新日期: 2023-09-27 18:18:48

查询返回以下数据:

+-------------------------------------+
| **SUM**                 **Account** |
+-------------------------------------+
| A & E FEE           182396          |
| CM FEE              108569          |
| CONTRACT VALUE      2256044         |
| OVERHEAD            197397          |
+-------------------------------------+

我需要选择对象属性AeFee, CmFee, ContractValue和Overhead，这将使属性成为列标题，所以我需要的数据看起来像:

+--------+--------+---------------+----------+
| AeFee  | CmFee  | ContractValue | Overhead |
+--------+--------+---------------+----------+
| 182396 | 108569 |       2256044 |   197397 |
+--------+--------+---------------+----------+

我尝试使用子选择来选择像这样的东西(伪代码)

Select(s => new
{
   AeFee = Sum if [Account] == "A & E FEE"
}

这个链接显示了我试图用SQL枢轴做什么。将行转换为列。SQL主

任何想法?

Linq转换或透视行到列

我总是喜欢回答"聚合"的问题。

  var result = data.Aggregate(
    // base object to store
    new { A = 0, CM = 0, CO = 0, O = 0},
    // add in an element
    (a,d) => {
    switch(d.sum) 
      {
         case "A & E FEE": 
           a.A += d.Account;
           break;
         case "CM FEE": 
           a.CM += d.Account;
           break;
           //etc
      }
      return a;
  });

注意你也可以——不知道d.s umm的可能值——你需要使用和展开对象。它看起来像这样(未测试)

  var result = data.Aggregate(
    // base object to store
    new ExpandoObject()
    // add in an element
    (a,d.sum.Replace(" ","_") => {
      a[d.sum.Replace(" ","_")] += d.Account;
      return a;
  });

如果您的sum字符串的值在属性标识符中无效，则此操作将不起作用。

这产生了我正在寻找的结果

var accounts = new List<string>()
                       {
                           "A & E FEE",
                           "cm fee",
                           "contract value",
                           "overhead"
                       };
        var commission = PJPTDSUMs
            .Where(p => p.Project.StartsWith("b29317")
                        && accounts.Contains(p.Acct)
                        && !p.Pjt_entity.StartsWith("05"))
            .GroupBy(c => c.Project)
            .Select(g => new
                         {
                             AeFee = g.Where(p => p.Acct == "A & E FEE").Sum(s => s.Eac_amount),
                             CmFee = g.Where(p => p.Acct == "cm fee").Sum(s => s.Eac_amount),
                             ContractValue = g.Where(p => p.Acct == "contract value").Sum(s => s.Eac_amount),
                             Overhead = g.Where(p => p.Acct == "overhead").Sum(s => s.Eac_amount),
                         });

+--------+--------+---------------+----------+
| AeFee  | CmFee  | ContractValue | Overhead |
+--------+--------+---------------+----------+
| 182396 | 108569 |       2256044 |   197397 |
+--------+--------+---------------+----------+
相关文章：