c# - LINQ - Static columns transposition into aggregate rows values -

currently have table looks below:

id    stockcompanyid    stockindexid    filterdateperiodid    q0     q1     q2     q3     q4     q5     q6     q7     q8     q9     q10     q11     q12     q13     q14     q15     q16     q17     q18     q19     q20 6301  4                 1               null                  null   null   null   0.14   0.14   0.14   0.14   null   null   null   null    0.05    0.05    0.05    0.05    null    null    null    null    null    null 6553  1                 1               null                  null   null   null   0.53   0.53   0.53   0.53   0.58   0.58   0.58   0.58    0.55    0.55    0.55    0.55    0.50    0.50    0.50    0.50    0.43    0.43 6805  2                 1               null                  null   null   null   0.00   0.00   0.00   0.00   0.05   0.05   0.05   0.05    0.08    0.08    0.08    0.08    0.06    0.06    0.06    0.06    0.07    0.07 7057  3                 1               null                  null   null   null   0.11   0.11   0.11   0.11   0.13   0.13   0.13   0.13    0.17    0.17    0.17    0.17    0.17    0.17    0.17    0.17    0.17    0.17 7309  5                 1               null                  null   null   null   0.16   0.16   0.16   0.16  -0.16  -0.16  -0.16  -0.16    0.19    0.19    0.19    0.19   -0.34   -0.34   -0.34   -0.34    0.00    0.00 

columns static design, keep 20q of financial data no more, , when new quarter comes move data left right , put new value q0

what need transposed version of above aggregated function.

for selected group of companies (collection of stockcompanyid), , provided stock index (stockindexid) calculate average value each quarter , present newest quarter latest quarter.

so final result should like:

quarter    aggregated value 0          0.53    <-- average(values matched companies , provided stock index) 1          0.71 2          0.81 3          ... 4          ... 5          ... 6          ... 7          ... 8          ... 9          ... 10         ... 11         ... 12         ... 13         ... 14         ... 15         ... 16         ... 17         ... 18         ... 19         ... 20         ... 

and sent data list of dto model:

public class dto {     public string quarter {get; set;}     public decimal aggregatedvalue {get; set;} }  list<dto> responsevalues 

can 1 me build linq query achieve requirement?

it's kludge, should work:

var matches = (   entry in table   entry.stockindexid == stockindexid   companies.contains(entry.stockcompanyid)   select entry).tolist();  var responsevalues = new list<dto>();  responsevalues.add(new dto {   quarter = 0,   aggregatedvalue = matches.sum(     match => match.q0 ?? 0   ) / matches.length }); responsevalues.add(new dto {   quarter = 1,   aggregatedvalue = matches.sum(     match => match.q1 ?? 0   ) / matches.length }); ... snip ... responsevalues.add(new dto {   quarter = 20,   aggregatedvalue = matches.sum(     match => match.q20 ?? 0   ) / matches.length });