How can i use cross Tab Query in c# linq?

Question

var TatalFeeCollectionDetail = new List<lcclsTotalFeeCollectionDetail>();
TatalFeeCollectionDetail = (from t in .......
                            group t by new { t.MonthName,t.FeeParticularName }                                        
                                into grp                                           
                                select new lcclsTotalFeeCollectionDetail                                           
                                {
                                    Month = grp.Key.MonthName,
                                    Particular = grp.First().FeeParticularLedgerName,
                                    Amount = grp.Sum(t => t.Amount),
                                }).ToList();
dgvTotalFeeCollectionDetail.DataSource = TatalFeeCollectionDetail;

My result is this .............

    Month   particular Amount   
    April       b      1    
    April       c      2    
    April       d      1    
    April       e      2
    April       f      1

I want to convert it like this can any1 help me...........I search for it but can,t understood what to do....

Month   b   c   d   e   f   Total Amount
April   1   2   1   2   1       7

Thanxxxxxxx in advance

May help : http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — Raphaël Althaus, Sep 17 '13 at 08:48
i Cant Get Anything Useful From above links..........I want do add columns Dynamically ....... — Coderz, Sep 17 '13 at 10:03
@SandeepSinghHada well, and if you have many months in your result, and different 'particular' for each month what do you want to get, then ? (for example, you have results for `b, c, d` in `April` and results for `d, e, f` for `May` ? — Raphaël Althaus, Sep 17 '13 at 13:14

score 2 · Accepted Answer · answered Sep 17 '13 at 11:43

You can't. That would mean creating an anonymous type with a variable number of properties, and it's not possible in C# which is strongly typed.

What you can certainly do is storing the 'particular' values in a Dictionary and get a result like

Month     particular                                  Total
April     {{b, 1}, {c, 2}, {d, 1}, {e, 2}, {f, 1}}    7

Mikey · Answer 2 · 2022-10-05T12:08:06.140

Nearly nine years after asking: You Could try a GroupJoin in a GroupBy

string[] particulars = TatalFeeCollectionDetail.Select(x => x.FeeParticularName ).Distinct().ToArray();
var crossTabbedData=TatalFeeCollectionDetail.GroupBy(TFCD => TFCD.Month).Select(TFCD => new {Month=TFCD.Key,RowData=particulars.GroupJoin(TFCD, p => p, t => t.FeeParticularName, (p, t) => t.Count() == 0 ? 0 : t.Sum(fn => fn.Amount)).ToList()}).ToList();

Should produce something like what you want, but perhaps without column headers.

How can i use cross Tab Query in c# linq?

2 Answers2