Pivot data in linq

Question

I've a data set which returns me this set of results:

Date      |Location |Amount
11.03.2011|Location1|  1000  
11.03.2011|Location2|  1000  
11.03.2011|Location3|  1000  
12.03.2011|Location1|  1000    
12.03.2011|Location2|  1000    
12.03.2011|Location3|  1000  
13.03.2011|Location4|  1000

I need to arrange my data in this way:

Location | 11.03.2011|12.03.2011|13.03.2011|    
Location1|       1000|      1000|         0|  
Location2|       1000|      1000|         0|  
Location3|       1000|      1000|         0|  
Location4|          0|         0|      1000|

Notice that: I don't know the dates in the rows so it's impossible for me to work with "if" clause (e.g.: if date == DateTime.Parse(11.03.2011)).

I hope that my explanation is clear.

See http://stackoverflow.com/questions/963491/pivot-data-using-linq — Magnus, May 23 '11 at 14:33
Try http://stackoverflow.com/search?tab=relevance&q=pivot%20linq — Conrad Frix, May 23 '11 at 22:35
http://stackoverflow.com/questions/565897/linq-query-for-creating-pivot-table — DhyanaRam, Dec 06 '11 at 22:43
possible duplicate of [Pivot an observable collection](http://stackoverflow.com/questions/7106548/pivot-an-observable-collection) — nawfal, Oct 17 '13 at 13:28

score 0 · Answer 1 · answered Apr 17 '12 at 12:39

Try this:

var Locations = Data.GroupBy(l => l.Location);
                .SelectMany( g => 
                             new 
                             { 
                                 LocationName = g.Key, 
                                 Amounts = g 
                             });

Func<Location, bool> matchMonth11 = l => l.Date == "11.03.2011";
Func<Location, bool> matchMonth12 = l => l.Date == "12.03.2011";
Func<Location, bool> matchMonth13 = l => l.Date == "13.03.2011";

var PivotedLocations = new List<PivotedLocation>();

foreach(var item in Locations )
{
    PivotedLocations.Add( new PivotedLocation
                          {
                              LocationName  = item.LocationName,
                              month11Amount = item.Amounts.Where(matchMonth11)
                                              .FirstOrDefault().Amount,
                              month12Amount = item.Amounts.Where(matchMonth12)
                                              .FirstOrDefault().Amount,
                              month13Amount = item.Amounts.Where(matchMonth13)
                                              .FirstOrDefault().Amount
                          });

}

You should first define the following class:

public class PivotedLocation
{
    public string LocationName { get; set; }
    public int month11Amount { get; set; }
    public int month12Amount { get; set; }
    public int month13Amount { get; set; }
}

Then the PivotedLocations list should contain the data in a pivoted form like what you want.

Pivot data in linq

1 Answers1