Linq Convert or Pivot Rows to Columns

Question

I have the following data returned from a query:

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

I need to select into object Properties AeFee, CmFee, ContractValue, and Overhead which would essentially make the properties the column headings so I need the data to look like:

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

I tried using a subselect to select something like this (pseudo code)

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

This link show exactly what I'm trying to do with a SQL pivot. Converts rows into columns. SQL Pivot

Any ideas?

Perhaps transforming it into a `Dictionary` would make more sense? — Matt Burland, Oct 22 '15 at 17:40
Also, do you already know the property names? Or does this have to be dynamic? If you already have a concrete class to map to, then this answer might be of use: http://stackoverflow.com/questions/4943817/mapping-object-to-dictionary-and-vice-versa — Matt Burland, Oct 22 '15 at 17:45
@RobertMcKee I have a MVC ViewModel where the rows are represented by Properties in the ViewModel. I need to display this ViewModel in a grid on my page, so I literally need the data to look (be shaped) in the form shown in question. — jslumar, Oct 22 '15 at 20:56

Hogan · Answer 1 · 2015-10-22T17:51:40.870

I always love a question where the answer is Aggregate.

  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;
  });

Note you can also - not know the possible values for d.sum in this way -- you would need to use and expando object. That would look something like this (NOT TESTED)

  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;
  });

This won't work if your sum strings have values which are not valid in property identifiers.

Did you check what EF Core generates for this? – DmitriBodiu Oct 04 '19 at 11:53 — DmitriBodiu, Oct 04 '19 at 11:53
@DmitriBodiu -- no since this question was not about EF – Hogan Oct 04 '19 at 13:19 — Hogan, Oct 04 '19 at 13:19

score 0 · Answer 2 · answered Oct 22 '15 at 21:41

This produced the results I was looking for

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 |
+--------+--------+---------------+----------+

Linq Convert or Pivot Rows to Columns

2 Answers2