LINQ TO SQL中的数据透视表

本文关键字：数据 透视 TO SQL LINQ | 更新日期: 2024-09-21 22:53:55

由于一些兼容问题，我无法在SQL server中使用Pivot，所以我正在尝试使用LINQ to SQL在代码级别执行同样的操作。

我有一个记录如下。

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

我正在尝试将其转换为

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

有人能帮我吗。

LINQ TO SQL中的数据透视表

这里有一个小的扩展方法，我用它来做你提到的事情。Thios是一个枢轴函数，它接受一个委托，并在内存中有数据后进行一些很好的分组：

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);
}

典型用法：

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

玩一玩，找到符合您标准的用法。它非常灵活，我已经在一系列不同的场景中使用过它，在这些场景中，其他循环方法会使它几乎不可能。

[btw]-你可以在我很久以前开始的一个线程上找到更多的linq小技巧，包括这个-这里：What'；你最喜欢的linq方法是什么；技巧'；

您将无法使用LINQ to SQL来执行整个查询，因为每一列都对应于结果类型上的一个属性，并且有未知数量的列，因此无法定义结果类型。在类似的情况下，我所做的就是定义一种类型：

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} 
}

CountsByDate属性对应于要创建的数据透视表中的多个日期列，键对应于列标题，值对应于单元格中的值。您必须分两步来填充字典，因为您无法在LINQ到SQL查询中创建Dictionary<T,U>。

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

ToList强制执行查询，因此下一部分发生在客户端（C#）端，在那里我们可以投影到字典中：

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())
    };

（如果你愿意，你可以在第一个查询中对日期/计数进行分组；我认为这样写要简单得多，但性能可能更差。）

这种模式的缺点是"pivot"中的每个"行"可能有不同数量的列，并且顺序是未定义的。如果您需要一致性，一个简单的选项是可以获得所有日期的列表，然后在pivotResult上循环，并为字典中没有的每个日期插入0。

相关文章：