How to do a rollup in Linq?

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 :)

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

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

1. Get all the distinct keys (var keys = myData.Select(d => d.Key).Distinct();)
2. 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
3. Iterate your distinct keys and months and add all the values to the dictionary so you effectively have a table ful of 0s
4. Iterate your data and do the relevant lookups and add to the numbers as necessary

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