Pivot in c# entity framework 3.5

Question

ISO Code description    Year    value
CAD Canadian Dollar     2009    1.3001
CAD Canadian Dollar     2010    1.3001
CAD Canadian Dollar     2011    0.0001
EUR Euro                2009    1.0000
EUR Euro                2010    1.0000
EUR Euro                2011    0.0001
USD US Dollar           2009    1.2300
USD US Dollar           2010    1.2300
USD US Dollar           2011    0.0001

Table 1

ISO Code    description 2009    2010    2011
CAD Canadian Dollar     1.3001  1.3001  0.0001
EUR Euro                1.0000  1.0000  0.0001
USD US Dollar           1.2300  1.2300  0.0001

Table 2

How can table 1 be converted to table 2 using linq in c#, provided that the number of years is dynamic ( it’s not fixed to 2009,2010, 2011, values 2012,2013 and so on can be added afterwards)

the classes are as following

class Currency
 {
    public string ISO Code { get; set; }
    public string Description { get; set; }
 }
class Rate
{        
    public string ISO Code { get; set; }
    public int Year { get; set; }
    public inr Value { get; set; }

}

In the end I have to bind the results to a gridview. Can someone please help

Answer 1

I think this cannot be done in Linq. Linq works with strongly typed objects so your result must be strongly typed but you are saying that it cannot be because another columns can be added over time (no dynamic behaviour in .NET 3.5).

You must query unpivoted data and compute pivoted values in memory and passing them to DataTable or you must call native SQL with PIVOT command (SQL Server 2005 and newer) and pass the result to your grid view.

This is typical scenario for SQL Server reporting services and their Matrix (SSRS 2005) or Tablix (SSRS 2008).

Edit:

Do you really need real pivot? There is no computation in your "pivoted" table. It looks like you just need this query:

var query = from c in ctx.Currencies.Include("Rates");

and transpose Rates when preparing data source for grid view.