Linq Group by to column

Question

I have a list of items with a property code and I want to group by eventId and create a table representation where code values are transformed to columns. Every triple (event, code,amount) is unique.

I want to transform this

eventId code  amount  
  1      A     100
  1      B     101
  1      C     102
  2      A     103
  2      C     104
  3      B     105
  ....

to this

eventId  A    B   C
 1      100  101 102
 2      103   0  104
 3       0   105  0
 ... 


var table=from x in list
    group x by x.eventId into gr
    select new
           {
              eventId=gr.Key,
              ....
           }

score 2 · Answer 1 · answered Mar 29 '17 at 10:48

2

You need to filter on grouped result and project in to anonymous object :

var table=from x in list
    group x by x.eventId into gr
    select new
           {
              eventId=gr.Key,
              A = gr.Where(x=>x.code == "A").Sum(x=>x.amount),
              B = gr.Where(x=>x.code == "B").Sum(x=>x.amount),
              C = gr.Where(x=>x.code == "C").Sum(x=>x.amount)
           }

answered Mar 29 '17 at 10:48

Ehsan Sajjad

61,834
16
105
160

Thank you, I don't know the codes from the beginning – albert Mar 29 '17 at 10:50
then you would need to group on the `code` as well – Ehsan Sajjad Mar 29 '17 at 10:53
then what's inside select (no need to display the sum, every triple is unique).? – albert Mar 29 '17 at 10:55
i will update my post to show how you can do that – Ehsan Sajjad Mar 29 '17 at 10:55
have a look at : http://stackoverflow.com/a/963755/1875256 – Ehsan Sajjad Mar 29 '17 at 11:00

score 0 · Answer 2 · answered Mar 29 '17 at 10:52

It's actually quite neat to use a lookup:

var table =
    from x in list
    group x by x.eventId into gr
    let lookup = gr.ToLookup(y => y.code, y => y.amount)
    select new
    {
        eventId = gr.Key,
        A = lookup["A"].Sum(),
        B = lookup["B"].Sum(),
        C = lookup["C"].Sum(),
    };

I get this result:

Linq Group by to column

2 Answers2