How to group and then pivot data

Question

I'm trying to group and then pivot some data to display on asp.net view.

Here is my View Model class.

public class myModel
{

    public string id { get; set; }
    public string fund{ get; set; }
    public string account{ get; set; }
    public string amount{ get; set; }

}

Here is a sample data returned from database

id   fund       account   amt
1     101       1001      25.70
2     101       1001      10.00
3     101       1001      12.00
4     101       1002      -5.0
5     201       2001      12.00
6     201       2001      11.00

Now I have a query that returns above data and maps it to the model mentioned above. Essentially I have a list of objects with above mentioned data.

Now I want to group and sum this data and display like below in my view.

fund
    account      sum of all amt
    account       sum of all amt
fund
    account       sum of all amt
    account       sum of all amt

so something like


101
   1001      47.00
   1002      -5

   ....
201
   2001   23

and so on

How do I go about doing this?

I have a list of objects as mentioned above.

I'm thinking of creating another class and them mapping to it but I'm perhaps making it more complicated than it needs to be

public class pivot
{
        public string fund { get; set; }
        public List < pivotdetail >detail{ get; set; }
 }

public class pivotdetail
{
       pretty obvious
}

Any ideas on how I can do this or I should approach this?

Have you tried LINQ? You might find this link helpful: https://stackoverflow.com/questions/7325278/group-by-in-linq?rq=1 — vvg, Jun 05 '19 at 22:00

Jeff Mercado · Answer 1 · 2019-06-05T22:11:11.123

0

You're creating a grouping of a grouping. First, group at the deepest level, then group those groups going up levels to get the structure you want.

from x in Data
group x.Amount by new { x.Fund, x.Account } into g
group new
{
    g.Key.Account,
    Amount = g.Sum(),
} by g.Key.Fund

Note that this will usually be inefficient as it will create new queries for every group. You might want to restrict it to a single grouping call on the database and further group in code.

from g in (from x in Data
          group x.Amount by new { x.Fund, x.Account } into g
          select new
          {
            g.Key.Fund,
            g.Key.Account,
            Amount = g.Sum()
          }).AsEnumerable()
group new
{
    g.Account,
    g.Amount,
} by g.Fund;

edited Jun 05 '19 at 22:11

answered Jun 05 '19 at 22:03

Jeff Mercado

129,526
32
251
272

I'm getting "IGrouping<, string>' does not contain a definition for 'Sum' and the best extension method overload 'Queryable.Sum(IQueryable)' "... This is for g.Sum(); the x.amount is decimal but the result is stored as var – Caesar Tex Jun 06 '19 at 20:19
Be sure to read the `amount` field as a decimal, otherwise convert to it. If read in as a string, this wouldn't work. – Jeff Mercado Jun 06 '19 at 20:23
I changed from var to class and it's still giving the same error. A bit of googling seems to suggest that sum can't be projected. – Caesar Tex Jun 06 '19 at 20:26

How to group and then pivot data

1 Answers1