Dynamic Pivot Linq C#

Question

I have the following collection / table

Category    Type       Detail     Cost

Auto        Hybrid     AC         80
Auto        Hybrid     Sunroof    100
Auto        Standard   AC         120
Motorcycle  Standard   Radio      60

Is there a way with linq to get this to pivot to look like this?

Category     Type      AC     Radio    Sunroof     
Auto         Hybrid    80     0        100 
Auto         Standard  120    0        0
Motorcycle   Standard  0      60       0

The details are dynamic so I can't hard code the value in the linq.

[Pivot data using LINQ](http://stackoverflow.com/questions/963491/pivot-data-using-linq) — Magnus, Jan 18 '12 at 16:47
I mean a new detail could be added to the table at any time, so the linq would have to include the new detail as a column — stillsmallvoice, Jan 18 '12 at 16:53
possible duplicate of [Is it possible to Pivot data using LINQ?](http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq) — nawfal, Oct 17 '13 at 13:43

score 2 · Answer 1 · answered Jan 18 '12 at 17:50

2

use the let keyword to generate a key for use with the group by clause like so:

var query = from item in list
            let key = new { Category = item.Category, Type = item.Type }
            group new { Detail = item.Detail, Cost = item.Cost } by key;

you can loop through the items returned from the query like so:

foreach(var item in query) {
    Console.WriteLine("{0} {1}: ", item.Key.Category, item.Key.Type);
    foreach(var detail in item) {
        Console.WriteLine("\t{0} {1}", detail.Detail, detail.Cost);
    }
}

it displays the following output

Auto Hybrid:
        AC 80
        Sunroof 100
Auto Standard:
        AC 120
Motorcycle Standard:
        Radio 60

answered Jan 18 '12 at 17:50

Charles Lambert

5,042
26
47

Thanks for the response! I'm working with your query to end up outputting a source that will bind to a gridview as in my example. – stillsmallvoice Jan 18 '12 at 17:58
Do you know of an easy way to do this Charles? I suppose I could just create a datatable within the for each statements you have there. – stillsmallvoice Jan 18 '12 at 18:27
If you are avoiding changing the UI control when more Detail values are added, then yes, you are going to have to dynamically create your control. You might look at the `Aggregate` method and pass in a `new GridView()` as the seed, and build it in there. That might be easier than using the loops. http://msdn.microsoft.com/en-us/library/bb549218.aspx – Charles Lambert Jan 18 '12 at 18:53

Dynamic Pivot Linq C#

1 Answers1