Suppose I have a call log DataTable where each row represents a call placed with the following columns: AccountNumber1, AccountNumber2, AccountListDate, AccountDisposition
I want to GroupBy column AccountNumber1 and want a new DataTable with the same columns 1 additional column NumCalls which will be the count of calls for each AccountNumber1.
New DataTable after GroupBy: AccountNumber1, AccountNumber2, AccountListDate, AccountDisposition, NumCalls
So far I have the following:
table.AsEnumerable()
.GroupBy(x => x.Field<int>("AccountNumber1"))
.Select(x => new { x.Key.AccountNumber1, NumCalls = x.Count() })
.CopyToDataTable()
Which gives me a DataTable with just two columns AccountNumber1 and NumCalls. How do I get the other columns as I described above?? I would appreciate any help. Thank you.
CodePudding user response:
There's no magic, you need to use a loop and initialize the new table with the new column:
DataTable tblResult = table.Clone();
tblResult.Columns.Add("NumCalls", typeof(int));
var query = table.AsEnumerable().GroupBy(r => r.Field<string>("AccountNumber1"));
foreach (var group in query)
{
DataRow newRow = tblResult.Rows.Add();
DataRow firstOfGroup = group.First();
newRow.SetField<string>("AccountNumber1", group.Key);
newRow.SetField<string>("AccountNumber2", firstOfGroup.Field<string>("AccountNumber2"));
newRow.SetField<DateTime>("AccountListDate", firstOfGroup.Field<DateTime>("AccountListDate"));
newRow.SetField<string>("AccountDisposition", firstOfGroup.Field<string>("AccountDisposition"));
newRow.SetField<int>("NumCalls", group.Count());
}
This takes arbitrary values from the first row of each group which seems to be desired.