convert rows into columns in linq

Question

I have the following query:

var query = from q1 in lstAgentDetails
            join q2 in lstAgentMaterialPercentage on q1.Agent_Id equals q2.AMP_Agent_Id
            into list1
            from lu in list1.DefaultIfEmpty()
            join q3 in lstMaterialType on lu.AMP_MaterialType_Id equals              q3.MaterialType_Id into list2
                        from l2 in list2.DefaultIfEmpty()
                        select new
                        {
                            q1.Agent_Name
                            lu.AMP_percentage
                            q3.Materialtype_Name
                        };   
            IList iu = query.ToList();

The query result is as follows:

agentname  material name     percentage
agent1     material1         20    
agent1     material2         10
agent2     material1         15
agent1     material3         25
agent3     material1         10
agent2     material2         30

However, I want the result in the following format:

agentname         material1         material2         material3   ...........
agent1            20                10                25          ..........
agent2            15                30                0           ..........
agent3            10                0                 0           ..........

Please help me out.

Answer 1

The second result will have dynamic number of columns for each agent hence it cannot be represented using a static type and will need to represented using Dictionary for each agent.

So, you can do something like : (after you get the query object from your code)

query.GroupBy(a => a.Agent_Name).Select(g => {
  var dict = new Dictionary<string,long>();
  foreach(var i in g)
     dict.Add(i.Materialtype_Name,i.AMP_percentage);
  return new {AgentName=g.Key, Materials = dict}
});