Using Linq to create crosstab results

Question

Possible Duplicate:
Is it possible to Pivot data using LINQ?

I'm wondering if its at all possible to create crosstab style results with Linq. I have some data that looks like the following:

    var list = new[]
    {
        new {GroupId = 1, Country = "UK", Value = 10},
        new {GroupId = 1, Country = "FR", Value = 12},
        new {GroupId = 1, Country = "US", Value = 18},
        new {GroupId = 2, Country = "UK", Value = 54},
        new {GroupId = 2, Country = "FR", Value = 55},
        new {GroupId = 2, Country = "UK", Value = 56}
    };

and I'm trying to output to a repeater control something like the following:

GroupId.....UK.....FR.....US
1...........10.....12.....18
2...........54.....55.....56

Its the dynamic columns that are causing my problems. Any solutions to this?

Answer 1

You need a runtimy class to hold these runtimy results. How about xml?

XElement result = new XElement("result",
  list.GroupBy(i => i.GroupId)
  .Select(g =>
    new XElement("Group", new XAttribute("GroupID", g.Key),
      g.Select(i => new XAttribute(i.Country, i.Value))
    )
  )
);

Are you expecting multiple records per result cell? If so there would need to be some Summing (and more grouping) in there.

(this answer is proof of concept, not final result. There's several issues to address, such as: ordering of columns, missing cells, and so on).