How to do a Pivot in Linq

Question

Possible Duplicate:
How can i made a pivot for this

I have a table as provided below

 ID     |     value     |    Name
--------+---------------+------------------
  1           Digital        Model
  1           companyA       Manufacturer
  2           Analog         Model
  2           CompanyB       Manufacturer
  3           Fiber          Model
  3           CompanyC       Manufacturer

I need to convert this back to 

 ID     |     Model    |     Manufacturer
--------+--------------+-------------------
 1           Digital          companyA           
 2           Analog           CompanyB        
 3            Fiber           CompanyC

Please help me with the Linq Query for this. Using T-SQL, I am able to convert it properly. However, with Linq I am finding problems writing Query.

take a look to my question about it http://stackoverflow.com/questions/8578547/how-can-i-made-a-pivot-for-this — Jorge, Jun 19 '12 at 15:11
There is no translation in Linq2Sql for the TSQL `Pivot` operator. — Magnus, Jun 19 '12 at 15:17

score 1 · Accepted Answer · answered Jun 19 '12 at 15:20

Try this:

 var results = Data.GroupBy(l => l.Name);
            .SelectMany( g => 
                         new 
                         { 
                             Metadata = g.Key, 
                             data = g 
                         });
var pivoted = new List<PivotedEntity>();

foreach(var item in results)
{
    pivoted.Add( 
        new PivotedEntity
        {
            Id  = item.Id,
            Model = item.data.Where(x => x.Name == "Model")
                        .FirstOrDefault().value,
            Manufacturer = item.data.Where(x => x.Name == "Manufacturer")
                         .FirstOrDefault().value,
        });
}

You should first define a new class:

public class PivotedEntity
{
    public int Id {get; set;}
    public string Model {get; set;}
    public string Manufacturer {get; set;}   
}

How to do a Pivot in Linq

1 Answers1

Linked