DataTable将结果分组在一行

本文介绍了DataTable将结果分组在一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述我有一个DataTable并且想要分组名称，姓氏和评论。其余的应该在同一行。在我的代码中，首先我将ID的值作为标题，然后组织每个ID的属性值。我想要的是使用其ID值对相同的名称，姓氏和注释进行分组。我的第一张表格如下所示：

ID名称Lastmame评论属性 1 kiki ha hello FF 3 lola mi hi AA 2 ka xe什么UU 2 kiki ha hello SS

我使用我的代码后：

姓名姓氏评论1 3 2 kiki ha hello FF lola mi hi AA ka xe什么UU kiki ha hello SS

我想要的是：

姓氏姓氏评论1 3 2 kiki ha hello FF SS lola mi hi AA ka xe什么UU

我的代码：

DataTable table1 = new DataTable （康德）; table1.Columns.Add（Comment，typeof（String））; table1.Columns.Add（Name，typeof（String））; table1.Columns.Add（Lastname，typeof（String））; DataTable comment = new DataTable（Comment）; comment.Columns.Add（ID，typeof（String））; comment.Columns.Add（Comment，typeof（String））; comment.Columns.Add（Attribute，typeof（String））; DataSet ds = new DataSet（DataSet）; ds.Tables.Add（table1）; ds.Tables.Add（comment）; object [] o1 = {hello，kiki，ha}; object [] o2 = {lola，mi}; object [] o3 = {what，ka，xe}; object [] c1 = {1，hello，FF}; object [] c2 = {3，AA}; object [] c3 = {2，what，UU}; object [] c4 = {2，hello，SS}; table1.Rows.Add（o1）; table1.Rows.Add（o2）; table1.Rows.Add（o3）; comment.Rows.Add（c1）; comment.Rows.Add（c2）; comment.Rows.Add（c3）; comment.Rows.Add（c4）; var results = from tb1 in comment.AsEnumerable（）连接tb2在table1.AsEnumerable（）在tb1.Field< string>（Comment）等于tb2。字段< string>（注释）选择新 { ID = tb1.Field< String>（ID）， Name = tb2.Field< String> （Name）， Lastname = tb2.Field< String>（Lastname），注释= tb1.Field< String>（Comment）， Attribute = tb1。字段< String>（Attribute）， }; DataTable result = LINQToDataTable（results）; var products = result.AsEnumerable（） .GroupBy（c => c [ID]） .Where（g =>！（g.Key是DBNull）） .Select（g =>（string）g.Key） .ToList（）; var newtable = result.Copy（）; products.ForEach（p => newtable.Columns.Add（p，typeof（string）））; foreach（newtable.AsEnumerable（）中的var row） { if（！（row [ID]是DBNull））row [（string）row [ ID]] = row [Attribute]; } newtable.Columns.Remove（ID）; newtable.Columns.Remove（Attribute）; var result11 = from newtable.AsEnumerable（） group t1 by new {Name = t1.Field< String>（Name），LastName = t1.Field< String> （LastName），Comment = t1.Field< String>（Comment），} into grp select new { Name = grp.Key.Name， LastName = grp.Key.LastName，注释= grp.Key.Comment， //这里的东西};

使用System.Reflection;

public DataTable LINQToDataTable< T>（IEnumerable< T> varlist） { DataTable dtReturn = new DataTable（）; //列名 PropertyInfo [] oProps = null; if（varlist == null）return dtReturn; foreach（T rec in varlist） { if（oProps == null） { oProps =（（Type）rec.GetType ））GetProperties（）; foreach（oProps中的PropertyInfo pi） {键入colType = pi.PropertyType; if（（colType.IsGenericType）&&（colType.GetGenericTypeDefinition（） == typeof（Nullable<））） { colType = colType.GetGenericArguments（）[0]; } dtReturn.Columns.Add（new DataColumn（pi.Name，colType））; } } DataRow dr = dtReturn.NewRow（）; foreach（oProps中的PropertyInfo pi） { dr [pi.Name] = pi.GetValue（rec，null）== null？ DBNull.Value：pi.GetValue （rec，null）; } dtReturn.Rows.Add（dr）; } return dtReturn; }

解决方案

根据另一个答案：

一种方法是将所有结构中的变量列（如字典）

为此，请使用以下查询：

var variableColumnNames = newtable.Columns.Cast< DataColumn>（） .Select（c => c.ColumnName） .Except（new [] { 名字，姓氏，评论））; var result11 = from newtable.AsEnumerable（） group t1 by new { Name = t1.Field< String>（Name）， LastName = t1.Field< String>（LastName），注释= t1.Field< String>（Comment），} into grp select new { grp.Key.Name， grp.Key.LastName， grp.Key.Comment，值= variableColumnNames.ToDictionary（ columnName => columnName， columnName => grp.Max（r => r.Field< String>（columnName）））};

如果您真的需要在类中具有可变数量的属性，那么这是不可能的如我所知，所以唯一可行的方法是将结果输出到另一个 DataTable （我们可以根据需要添加任意列）。

方法＃2 - 使用动态

LINQ查询：

var result11 = from t1 in newtable.AsEnumerable（） group t1 by new { Name = t1.Field< String>（Name）， LastName = t1.Field< String>（LastName），注释= ），} into grp 选择CreateNewDynamicObject （ grp.Key.Name， grp.Key.LastName， grp.Key.Comment， variableColumnNames.ToDictionary（ columnName => columnName， columnName => grp.Max（r => r。字段< String>（columnName））））; }

创建动态对象的新方法：

私有静态动态CreateNewDynamicObject（ string name，string lastName，string comment，Dictionary< string，string> customProperties） { dynamic obj = new ExpandoObject（）; obj.Name = name; obj.LastName = lastName; obj.Comment = comment; foreach（customProperties中的var prop）（obj as IDictionary< string，Object>）。Add（prop.Key，prop.Value ??）; return obj; }

方法＃3 - 输出到 DataTable

生成的 DataTable （ destinationTable ）可以用作 DataGridView 的源代码：

I have a DataTable and want to group Name, LastName and Comment. The rest should be in the same row. In my Code firstly i make ID's values as header and then organize the Attribute values to each ID. What I want here is to group the the same Name, Lastname and Comment with their ID values. My first Table looks like that:

ID Name Lastmame Comment Attribute 1 kiki ha hello FF 3 lola mi hi AA 2 ka xe what UU 2 kiki ha hello SS

After i use my code:

Name Lastname Comment 1 3 2 kiki ha hello FF lola mi hi AA ka xe what UU kiki ha hello SS

what i want to have is:

Name Lastname Comment 1 3 2 kiki ha hello FF SS lola mi hi AA ka xe what UU

My Code:

DataTable table1 = new DataTable("Kunde"); table1.Columns.Add("Comment", typeof(String)); table1.Columns.Add("Name", typeof(String)); table1.Columns.Add("Lastname", typeof(String)); DataTable comment = new DataTable("Comment"); comment.Columns.Add("ID", typeof(String)); comment.Columns.Add("Comment", typeof(String)); comment.Columns.Add("Attribute", typeof(String)); DataSet ds = new DataSet("DataSet"); ds.Tables.Add(table1); ds.Tables.Add(comment); object[] o1 = { "hello", "kiki", "ha" }; object[] o2 = { "hi", "lola", "mi" }; object[] o3 = { "what", "ka", "xe" }; object[] c1 = { 1, "hello", "FF" }; object[] c2 = { 3, "hi", "AA" }; object[] c3 = { 2, "what", "UU" }; object[] c4 = { 2, "hello", "SS" }; table1.Rows.Add(o1); table1.Rows.Add(o2); table1.Rows.Add(o3); comment.Rows.Add(c1); comment.Rows.Add(c2); comment.Rows.Add(c3); comment.Rows.Add(c4); var results = from tb1 in comment.AsEnumerable() join tb2 in table1.AsEnumerable() on tb1.Field<string>("Comment") equals tb2.Field<string>("Comment") select new { ID = tb1.Field<String>("ID"), Name = tb2.Field<String>("Name"), Lastname = tb2.Field<String>("Lastname"), Comment = tb1.Field<String>("Comment"), Attribute = tb1.Field<String>("Attribute"), }; DataTable result = LINQToDataTable(results); var products = result.AsEnumerable() .GroupBy(c => c["ID"]) .Where(g => !(g.Key is DBNull)) .Select(g => (string)g.Key) .ToList(); var newtable = result.Copy(); products.ForEach(p => newtable.Columns.Add(p, typeof(string))); foreach (var row in newtable.AsEnumerable()) { if (!(row["ID"] is DBNull)) row[(string)row["ID"]] = row["Attribute"]; } newtable.Columns.Remove("ID"); newtable.Columns.Remove("Attribute"); var result11 = from t1 in newtable.AsEnumerable() group t1 by new { Name = t1.Field<String>("Name"), LastName = t1.Field<String>("LastName"), Comment = t1.Field<String>("Comment"), } into grp select new { Name = grp.Key.Name, LastName = grp.Key.LastName, Comment = grp.Key.Comment, //Something here };

using System.Reflection;

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist) { DataTable dtReturn = new DataTable(); // column names PropertyInfo[] oProps = null; if (varlist == null) return dtReturn; foreach (T rec in varlist) { if (oProps == null) { oProps = ((Type)rec.GetType()).GetProperties(); foreach (PropertyInfo pi in oProps) { Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>))) { colType = colType.GetGenericArguments()[0]; } dtReturn.Columns.Add(new DataColumn(pi.Name, colType)); } } DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps) { dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue (rec, null); } dtReturn.Rows.Add(dr); } return dtReturn; }

解决方案

Based on the comments to this other answer:

One approach would be to stuff all the variable columns in a structure (like a dictionary).

In order to do this, use the following query:

var variableColumnNames = newtable.Columns.Cast<DataColumn>() .Select(c => c.ColumnName) .Except(new[]{"Name", "Lastname", "Comment"}); var result11 = from t1 in newtable.AsEnumerable() group t1 by new { Name = t1.Field<String>("Name"), LastName = t1.Field<String>("LastName"), Comment = t1.Field<String>("Comment"), } into grp select new { grp.Key.Name, grp.Key.LastName, grp.Key.Comment, Values = variableColumnNames.ToDictionary( columnName => columnName, columnName => grp.Max(r => r.Field<String>(columnName))) };

If you really need to have a variable number of properties in the class, this is not possible as far as I know, so the only plausible way to do that is to output the result to another DataTable (to which we can add as many columns as we want).

Approach #2 - using dynamic

The LINQ query:

var result11 = from t1 in newtable.AsEnumerable() group t1 by new { Name = t1.Field<String>("Name"), LastName = t1.Field<String>("LastName"), Comment = t1.Field<String>("Comment"), } into grp select CreateNewDynamicObject ( grp.Key.Name, grp.Key.LastName, grp.Key.Comment, variableColumnNames.ToDictionary( columnName => columnName, columnName => grp.Max(r => r.Field<String>(columnName))) ); }

the new method that creates the dynamic object:

private static dynamic CreateNewDynamicObject( string name, string lastName, string comment, Dictionary<string, string> customProperties) { dynamic obj = new ExpandoObject(); obj.Name = name; obj.LastName = lastName; obj.Comment = comment; foreach (var prop in customProperties) (obj as IDictionary<string, Object>).Add(prop.Key, prop.Value ?? ""); return obj; }

Approach #3 - outputting to a DataTable

The resulting DataTable (destinationTable) can be used as a source for a DataGridView:

更多推荐

DataTable将结果分组在一行