How to do a rollup in Linq?

Question

Apologies if this has been asked before, but I'm not sure if I've asked the correct question. Here's my problem:

I have data such as

AA01 JAN 100
AA01 JAN 200
AA01 FEB 200
AA02 JAN 50
AA03 FEB 500
AA03 MAR 250
AA03 MAR 75

And I'd like to get this into a result IEnumerable looking something like:

Result{
  string Key,
  int Jan,
  int Feb,
  int Mar,
  ...
 }

IEnumerable<Result> results = .....
KEY   JAN   FEB   MAR
AA01  300   200     0
AA02   50     0     0
AA03    0   500   325

The month columns are fixed if that helps, but I can't work out how to get a linq query to produce these results? I can repeatedly iterate the data and add to the results in standard loops, but this sounds like the wrong way to be going.

Thanks for any assistance :)

Check here http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — Hukam, Aug 24 '11 at 06:58

score 1 · Answer 1 · answered Aug 24 '11 at 06:59

I'm not sure how to do this directly in Linq, but one way would be to do the following:

Get all the distinct keys (var keys = myData.Select(d => d.Key).Distinct();)
Create a Dictionary<String, Dictionary<String, Int32>> - this will hold your key as the key to the first dictionary, and the month as your key to the second
Iterate your distinct keys and months and add all the values to the dictionary so you effectively have a table ful of 0s
Iterate your data and do the relevant lookups and add to the numbers as necessary

score 1 · Accepted Answer · answered Aug 24 '11 at 08:33

Here's the simplest way I know how:

var query =
    from r in records
    group r by r.Key into grs
    let ms = grs.ToLookup(gr => gr.Month, gr => gr.Value)
    select new
    {
        grs.Key,
        JAN = ms["JAN"].Sum(),
        FEB = ms["FEB"].Sum(),
        MAR = ms["MAR"].Sum(),
        // ...
        DEC = ms["DEC"].Sum(),
    };

An alternative which is more flexible from a computational standpoint by not exactly in the format you want is to do this:

var months = Enumerable
    .Range(0, 12)
    .Select(m =>
        String
            .Format("{0:MMM}", new DateTime(2011, m + 1, 1))
            .ToUpper());

var query =
    from r in records
    group r by r.Key into grs
    let ms = grs.ToLookup(gr => gr.Month, gr => gr.Value)
    select new
    {
        grs.Key,
        Months =
            from m in months
            select ms[m].Sum(),
    };

This worked for a much larger set of sums than the other version. — BlueChippy, Aug 24 '11 at 09:41

score 0 · Answer 3 · answered Feb 03 '17 at 08:28

0

Quite interesting solution is provided here

https://blogs.msdn.microsoft.com/mitsu/2007/12/21/playing-with-linq-grouping-groupbymany/

It describes how to perform groupbby by several properties. I.e:

var result = customers.GroupByMany(c => c.Country, c => c.City);

answered Feb 03 '17 at 08:28

Igor S

98
2
9

The link is broken. – Rodrigo Rodrigues May 31 '21 at 14:22

How to do a rollup in Linq?

3 Answers3