使用 LINQ 的动态枢轴是否可能? [英] Is a dynamic pivot using LINQ possible?
问题描述
我有一个 T-SQL 2005 查询，它返回:
I have a T-SQL 2005 query which returns:
pid propertyid displayname value
----------- ----------- --------------- ---------------
14270790 74 Low Price 1.3614
14270790 75 High Price 0
14270791 74 Low Price 1.3525
14270791 75 High Price 0
14270792 74 Low Price 1.353
14270792 75 High Price 0
14270793 74 Low Price 1.3625
14270793 75 High Price 0
14270794 74 Low Price 1.3524
14270794 75 High Price 0
我想做的基本上是以 displayname 字段为中心，希望能产生:
What I would like to do is essentially pivot on the displayname field, hopefully producing:
pid Low Price High Price
14270790 1.3614 0
14270791 1.3525 0
14270792 1.353 0
14270793 1.3625 0
14270794 1.3524 0
(不确定 propertyid 字段将如何输出，所以我把它省略了(希望它只是放在 Low Price 和 High Price 字段旁边，以指示它们的 ID，但我认为这不会起作用.)
(Not sure how the propertyid field would be output, so I left it out (was hoping it would simply sit alongside the Low Price and High Price fields, to indicate their IDs, but I don't think that will work.)
问题在于原始 displayname 字段的内容是动态的 - 它是通过与 PropertyName' 表的连接产生的，因此透视列的数量是可变的.因此，它可能包含High Price、Low Price、Open和Close`，具体取决于与该表的连接返回什么.
The problem is that the content of the original displayname field is dynamic - it is produced from a join with a PropertyName' table, so the number of pivoted columns is variable. It could therefore containHigh Price,Low Price,OpenandClose`, depending on what the join with that table returns.
当然，在固定查询或存储过程中生成此枢轴相对容易(不管我在编写初始查询时遇到的麻烦！).但是，是否有可能让 LINQ 生成一个 SQL 查询来命名要生成的每一列，而不是编写一个列出列名的动态(可能在存储过程中)查询?
It is, of course, relatively easy (regardless of the trouble I'm having writing the initial query!) to produce this pivot in a fixed query or stored proc. However, is it possible to get LINQ to generate a SQL query which would name each column to be produced rather than having to write a dynamic (probably in a stored proc) query which lists out the column names?
谢谢，
马特.
推荐答案
我会给你一个包含不同数据的样本(我需要的).您可以根据自己的需要进行调整.注意只使用了两个 linq 查询，其他大多数绒毛是将列表转换为数据表.
I'll give you a sample with a different data (that I needed). You can adapt that to your need. Note only two linq queries are used, most of the other fluff is to convert a list into a datatable.
var data = new[] {
new{Student=1, Subject="English", Marks=40},
new{Student=1, Subject="Maths", Marks=50},
new{Student=1, Subject="Science", Marks=60},
new{Student=1, Subject="Physics", Marks=70},
new{Student=1, Subject="Chemistry", Marks=80},
new{Student=1, Subject="Biology", Marks=90},
new{Student=2, Subject="English", Marks=4},
new{Student=2, Subject="Maths", Marks=5},
new{Student=2, Subject="Science", Marks=6},
new{Student=2, Subject="Physics", Marks=7},
new{Student=2, Subject="Chemistry", Marks=8},
new{Student=2, Subject="Biology", Marks=9}
};
/*Here the pivot column is the subject and the static column is student
group the data against the static column(s)*/
var groups = from d in data
group d by d.Student into grp
select new
{
StudentId = grp.Key,
Marks = grp.Select(d2 => new { d2.Subject, d2.Marks }).ToArray()
};
/*get all possible subjects into a separate group*/
var subjects = (from d in data
select d.Subject).Distinct();
DataTable dt = new DataTable();
/*for static cols*/
dt.Columns.Add("STUDENT_ID");
/*for dynamic cols*/
foreach (var subject in subjects)
{
dt.Columns.Add(subject.ToString());
}
/*pivot the data into a new datatable*/
foreach (var g in groups)
{
DataRow dr = dt.NewRow();
dr["STUDENT_ID"] = g.StudentId;
foreach (var mark in g.Marks)
{
dr[mark.Subject] = mark.Marks;
}
dt.Rows.Add(dr);
}
这篇关于使用 LINQ 的动态枢轴是否可能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
