Pivot table in LINQ TO SQL

Question

I am unable to use Pivot in SQL server due to some compatible issues, so I am trying do same thing in code level by using LINQ TO SQL.

I have a record set as like below.

Dates           RM      DM      LocationNum     City        State   Count
-----           --      --      -----------     ----        -----   -----
2013-12-13      1       1       4795            Grapevine   TX      1
2013-12-13      1       2       4796            Grapevine   TX      1
2013-12-14      2       3       4797            Grapevine   TX      1
2013-12-15      NULL    NULL    NULL            NULL        NULL    0
2013-12-16      NULL    NULL    NULL            NULL        NULL    0

I am trying to convert this in

RM  DM  Loc     City        2013-12-13      2013-12-14  2013-12-15  2013-12-16  
--  --  ---     ----        ----------      ----------  ----------  ----------
1   1   4795    City1       1               0           0           0
1   2   4796    City2       1               0           0           0
1   3   4797    City3       0               1           0           0

Can any one help me in this.

score 3 · Accepted Answer · edited May 23 '17 at 12:19

here's a little extension method that I use to do exactly what you mention. Thios is a pivot function that takes a delegate and does some nice grouping once it has the data in memory:

public static Dictionary<TKey1, Dictionary<TKey2, TValue>> Pivot<TSource, TKey1, TKey2, TValue>
    (
        this IEnumerable<TSource> source, 
        Func<TSource, TKey1> key1Selector, 
        Func<TSource, TKey2> key2Selector,
        Func<IEnumerable<TSource>, TValue> aggregate
    )
{
    return source.GroupBy(key1Selector).Select(
    x => new
    {
        X = x.Key,
        Y = x.GroupBy(key2Selector).Select(
        z => new
        {
            Z = z.Key,
            V = aggregate(z)
        }
        ).ToDictionary(e => e.Z, o => o.V)
    }
    ).ToDictionary(e => e.X, o => o.Y);
}

typical usage:

var pivotResult = itemsFromPreviousQuery.Pivot(s => s.SeasonID, 
                s => s.FundPropertyEntity.PropertyEntity.PropertyName, 
                lst => lst.Count());

play around with it to find the usage tthat fits ytour criteria. It's pretty flexible and I've used it across a range of different scenarios where other looping methods would have made it almost impossible.

[btw] - you can find more little linq tricks on a thread that I started way back, this one included - here: What's your favourite linq method or 'trick'

score 0 · Answer 2 · answered Dec 14 '13 at 12:12

You won't be able to use LINQ to SQL to execute the entire query, because each column corresponds to a property on the result type and there are an unknown number of columns, so there is no way to define the result type. What I've done in similar situations like is define a type like this:

public class PseudoPivotRow
{
    public string RM {get; set;}
    public string DM {get; set;}
    public int Loc {get; set;}
    public string City {get; set;}
    public Dictionary<DateTime, int> CountsByDate{get; set} 
}

The CountsByDate property corresponds to the multiple date columns in the pivot you want to create, with the key corresponding to the column header and the value the value in the cell. You have to populate the dictionary in two steps, because you can't create a Dictionary<T,U> in a LINQ to SQL query.

var materializedGroups = 
  db.SourceTable
    .GroupBy(s => new { s.RM, s.DM, s.LocationNum, s.City },
             s => new { s.Dates, s.Count })
    .ToList();

The ToList forces the query to be executed, so the next part happens on the client (C#) side, where we can project into a dictionary:

var pivotResult = 
  materializedGroups
    .Select(mg => new PseudoPivotRow
    {
        RM = mg.Key.RM,
        DM = mg.Key.DM,
        Loc = mg.Key.LocationNum,
        CountsByDate = mg.GroupBy(r => r.Dates, r => r.Count)
                        .ToDictionary(g => g.Key, g => g.Sum())
    };

(You could do the grouping of dates/counts in the first query if you wanted; I think it's a lot simpler to write this way, but performance may be worse.)

The downside to this pattern is that each "row" in the "pivot" may have a different number of columns, and the order is undefined. If you need consistency, one simple option is to can get a list of all the dates, then loop over pivotResult and insert 0s for every date not in the dictionary.

Pivot table in LINQ TO SQL

2 Answers2