Generating list in a following format

Question

I have scenario where i will receive the header names (for creating text file) from database. The list will be like

DeptId TeacherId StudentId testname testvalue

  101   1           501     A         85
  101   1           501     B         90
  101   1           502     A         80
  101   1           502     B         75
  101   2           502     A         80
  101   2           502     B         84
  102   1           503     A         56
  102   1           503     B         60

Assuming that the number of test will be same ( A & B) The output should be like

DeptId TeacherId StudentId   A   B
101     1         501       85   90
101     1         502       80   75
101     2         502       80   84
102     1         503       56   60

And what is your actual question? What exactly are you having problems with? What have you tried so far, and where exactly are you stuck? — bassfader, Sep 14 '16 at 12:05
This is called a pivot. You can google that to find ways to achieve it in SQL or in code. — juharr, Sep 14 '16 at 12:06
Just look here: http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — A.B., Sep 14 '16 at 12:12

juharr · Accepted Answer · 2016-09-14T12:20:10.777

Something like this would work

var results = from x in context.Tests
              group x by new { x.DeptId, x.StudentId, x.TeacherId } into grp
              select new 
              {
                  grp.Key.DeptId,
                  grp.Key.StudentId,
                  grp.Key.TeacherId,
                  A = grp.FirstOrDefault(x => x.TestName == "A")?.TestValue,
                  B = grp.FirstOrDefault(x => x.TestName == "B")?.TestValue
              };

This requires C# 6 for the null-conditional operator ?., but you could do this instead

A = grp.Where(x => x.TestName == "A").Select(x => x.TestValue).FirstOrDefault();

You might also want to filter out other tests with a where after the from to avoid results were both A and B would end up being null, like this

where x.TestName == "A" || x.TestName == "B"

Of course you could still end up with null values for A and B if the DeptId-StudentId-TeacherId combination only has one of those tests in your DB.

We are not sure about the test names until we get those from database — Silly Volley, Sep 14 '16 at 12:35
@SillyVolley In that case you could do something like `Tests = grp.ToDictionary(x => x.TestName, x => x.TestValue)` instead. If you don't know the column names then it's not going to be easy to create them. — juharr, Sep 14 '16 at 12:56

Generating list in a following format

1 Answers1