Pivot list data using LINQ

Question

I am new to LINQ, but I am wondering if it is possible to use LINQ to pivot data from the following layout:

cvMesFicFin cvAnoFicFin cvVlrBasFicFin
08          1998            30
09          1998            30
10          1998            30
11          1998            30
12          1998            30
01          1999            30
02          1999            30
03          1999            30
04          1999            30
05          1999            30
06          1999            30

into something like this:

Year    01  02  03  04  05  06  07  08  09  10  11  12
1998    NULL    NULL    NULL    NULL    NULL    NULL    NULL    30  30  30  30  30
1999    30  30  30  30  30  30  NULL    NULL    NULL    NULL    NULL    NULL

It would be helpful if you gave some information about what data structures you are using. e.g. is the first table a `List` or a CSV file? What kind of data structure do you want the results in? — markmuetz, Mar 27 '12 at 20:28
@Henk: apparently, the OP has an `IEnumerable` of objects consisting of fields `cvMesFicFin`, `cvAnoFicFin` and `cvVlrBasFicFin` (and unknown methods). — Vlad, Mar 27 '12 at 20:37

score 2 · Answer 1 · edited May 23 '17 at 10:34

I would use something like this:

var r = a.GroupBy(e => e.cvAnoFicFin).Select(g => new
{
    Year = g.Key,
    Jan = g.Where(e => e.cvMesFicFin == 1)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Feb = g.Where(e => e.cvMesFicFin == 2)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Mar = g.Where(e => e.cvMesFicFin == 3)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Apr = g.Where(e => e.cvMesFicFin == 4)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    May = g.Where(e => e.cvMesFicFin == 5)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Jun = g.Where(e => e.cvMesFicFin == 6)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Jul = g.Where(e => e.cvMesFicFin == 7)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Aug = g.Where(e => e.cvMesFicFin == 8)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Sep = g.Where(e => e.cvMesFicFin == 9)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Oct = g.Where(e => e.cvMesFicFin == 10)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Nov = g.Where(e => e.cvMesFicFin == 11)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault(),
    Dec = g.Where(e => e.cvMesFicFin == 12)
           .Select(c => (int?)c.cvVlrBasFicFin).SingleOrDefault()
});

The idea is stolen from this answer: https://stackoverflow.com/a/167937/276994.

If the data represent the database entries, than cvVlrBasFicFin is perhaps already nullable, so the cast is not needed.

Pivot list data using LINQ

1 Answers1