CODEWITHSUNDEEP
linqravendb
Bobby
Bobby

Reputation: 1604

Transform Linq Data Structure

I am hoping you can help.

I currently have a data structure like so I got after group by (on name year and state) linq aggregation query (this can not be changed)

Name  Year  State  Total

Bobby 2015  East   5

Bobby 2015  West   5

Bobby 2015  North  10

Paul  2015  East   15

How can I transform above structure using linq to below structure.

Name  Year  East  West  North  Total

Bobby  2015   5      5      10       20

Paul   2015   15     0      0        15

Keeping in mind I am trying to avoid using If statements like state=East ? Add east value : 0 in my linq groupby to get desired structure.

Thanks for help in advance.

Upvotes: 0

Views: 67

Answers (1)

D Stanley
D Stanley

Reputation: 152566

Here's one way to do a pivot in Linq:

var pivot = data
    .GroupBy(d => new {d.Name, d.Year})
    .Select(g => new {
        Name = g.Key.Name,
        Year = g.Key.Year,
        East = g.Where(c => c.State == "East").Sum(c => c.Total),
        West = g.Where(c => c.State == "West").Sum(c => c.Total),
        North = g.Where(c => c.State == "North").Sum(c => c.Total),
        South = g.Where(c => c.State == "South").Sum(c => c.Total),
        Total = g.Sum(c => c.Total),
    });

You can do more dynamic pivots using DataTables (so you can add columns dynamically) but if your columns are known at design-time this method is cleaner IMHO.

Upvotes: 1

Related Questions