使用多个表，Linq to Sql，GridView从背后的代码动态创建复杂指标(PIVOT)表 [英] Dynamically creating complex metrics (PIVOT) table from code behind using multiple tables, Linq to Sql, GridView

问题描述

我花了很多时间环顾四周，但是找不到真正的解决方案:

我有4个表:类别(id，Name)，产品(id，Name，CategoryID)， RatedProducts (id，Rating ，ProductID，UserID)，用户(id，名称)

I''ve spent significant amount of time looking around, however couldn''t really find any solution to this:

I''ve got 4 tables: Category(id,Name),Product(id,Name,CategoryID),RatedProducts(id,Rating,ProductID,UserID), User(id,Name)

              Category1                        Category2
Name  |   Product1  Product2  Product3    Product4   Product5   Product6
---------------------------------- ----------------------------------
Joe   |  Excellent   Good   Not Rated     Good       Good     Excellent
Jill  |    Good      Poor      Good       Poor       Poor       Good

请注意，产品列表会随着时间以及等级和用户的变化而变化，因此应该全部动态填充. GridView必须是可更新的，用户应该能够通过更改下拉列表中的值来更新Rating.

我宁愿使用LINQ而不是SQL，但可以采用其他方法.

Mind that product list changes over time as well as ratings and users, so it should all be dynamically populated. GridView must be updatable, user should be able to update Rating by changing the value in a dropdown.

I would prefer LINQ to SQL but any other way is ok.

推荐答案

我有关动态旋转的文章

my article about dynamic pivotting explains[^] it all.

感谢，

Thanks,

var result = from r in smdt.SM_RatedProducts
                   group r by new { r.SM_Product.Name, UserName = r.SM_User.Name, Category = r.SM_Product.SM_Category.Name, r.Rating } into prodGroup
                   orderby prodGroup.Key.UserName ascending
                   orderby prodGroup.Key.Category ascending
                   select new { prodGroup.Key.UserName, prodGroup.Key.Category, prodGroup.Key.Name, prodGroup.Key.Rating };

我的应该不会那么复杂.

任何人都可以给我一个关于如何实现上述目标的小提示?

Mine one should not be as complicated though.

Anyone could give me a tiny hint on how to achieve above?

这篇关于使用多个表，Linq to Sql，GridView从背后的代码动态创建复杂指标(PIVOT)表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！