是否可以使用LINQ进行数据透视?

问题描述

我想知道是否可以使用LINQ从以下布局中透视数据:

I am wondering if it is possible to use LINQ to pivot data from the following layout:

CustID | OrderDate | Qty
1      | 1/1/2008  | 100
2      | 1/2/2008  | 200
1      | 2/2/2008  | 350
2      | 2/28/2008 | 221
1      | 3/12/2008 | 250
2      | 3/15/2008 | 2150

变成这样:

CustID  | Jan- 2008 | Feb- 2008 | Mar - 2008 |
1       | 100       | 350       |  250
2       | 200       | 221       | 2150

推荐答案

像这样吗?

List<CustData> myList = GetCustData();

var query = myList
    .GroupBy(c => c.CustId)
    .Select(g => new {
        CustId = g.Key,
        Jan = g.Where(c => c.OrderDate.Month == 1).Sum(c => c.Qty),
        Feb = g.Where(c => c.OrderDate.Month == 2).Sum(c => c.Qty),
        March = g.Where(c => c.OrderDate.Month == 3).Sum(c => c.Qty)
    });

Linq中的

GroupBy与SQL的工作方式不同.在SQL中，您可以获取键和聚合(行/列形状).在Linq中，您将获得密钥和任何元素作为密钥的子级(层次形状).要进行透视，必须将层次结构投影回您选择的行/列形式.

GroupBy in Linq does not work the same as SQL. In SQL, you get the key and aggregates (row/column shape). In Linq, you get the key and any elements as children of the key (hierarchical shape). To pivot, you must project the hierarchy back into a row/column form of your choosing.

这篇关于是否可以使用LINQ进行数据透视?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！