DataTable group the result

Question

I want to group in Datatable by Name, LastName and the rest should be in same row. Can someone help me with it?

My DataTable:

 Name   LastName    1    3    2
 kiki    ha         FF
 lola    mi             AA
 ka      xe                   UU
 kiki    ha                   SS

I want to have DataTable group by Name:

Name   LastName   1    3     2
kiki    ha        FF         SS 
lola    mi             AA 
ka      xe                   UU

My new code:

var result11 = from t1 in newtable.AsEnumerable()
               group t1 by new { Name = t1.Field<String>("Name"), LastName = t1.Field<String>("LastName") } into grp
               select new
               {
                   Name = grp.Key.Name,
                   LastName = grp.Key.LastName,

                   //Something must be there     
               };

What if there will be conflict? If column 1 will have values in several rows with equal Name and LastName? — Kirill Bestemyanov, Oct 16 '12 at 12:07
no, in my table column 1,3 and 2 would have just one value for the same Name and LastName like this table, wont be a conflict. Please take "My DataTable" as fixed table — Uni Le, Oct 16 '12 at 12:13

Cristian Lupascu · Accepted Answer · 2012-10-16T12:16:11.640

2

Add these lines instead of the comment (//something must be there):

C1 = String.Join(",", grp.Select(r=>r.Field<String>("1"))),
C2 = String.Join(",", grp.Select(r=>r.Field<String>("2"))),
C3 = String.Join(",", grp.Select(r=>r.Field<String>("3")))

to get three new columns on the output that aggregate values from the columns 1, 3 and 2.

If you have multiple values in one of the columns for a group, all the values will be shown and separated by comma (,).

If you are sure that there's at most one value per column per group, then you can simply do:

C1 = grp.Max(r => r.Field<String>("1")),
C3 = grp.Max(r => r.Field<String>("3")),
C2 = grp.Max(r => r.Field<String>("2"))

edited Oct 16 '12 at 12:16

answered Oct 16 '12 at 12:10

Cristian Lupascu

39,078
16
100
137

one question more: the same example but if i dont know how many colums i have? like 1,3,2,4,8 ... how would i do it? – Uni Le Oct 16 '12 at 12:39
in this case this approach does not work, because you don't know how many columns (properties) you need in the output (anonymous) type. Try outputting to another `DataTable` instead. – Cristian Lupascu Oct 16 '12 at 13:38
@UniLe or, instead of N columns you can output a `Dictionary`, where you can use the column name as key and the `grp.Max(r => r.Field(colName))` as value – Cristian Lupascu Oct 16 '12 at 13:40
hmm... it's a little complicated, can you show me in my new thread? http://stackoverflow.com/questions/12916110/datatable-group-the-result-in-one-row – Uni Le Oct 16 '12 at 13:47

score 1 · Answer 2 · answered Oct 16 '12 at 12:31

1

If you want a DataTable as result, this gives your desired result:

var lastNameGroups = from row in table1.AsEnumerable()
                     group row by new {
                        Name= row.Field<String>("Name"), 
                        LastName = row.Field<String>("LastName")
                     } into LastNameGroups
                     select LastNameGroups;

var table2 = table1.Clone();
foreach (var lng in lastNameGroups)
{
    var row = table2.Rows.Add();
    row.SetField("Name", lng.Key.Name);
    row.SetField("LastName", lng.Key.LastName);
    var ones = lng.Where(r => !string.IsNullOrEmpty(r.Field<String>("1")));
    if(ones.Any())
        row.SetField("1", ones.First().Field<String>("1"));
    var twos = lng.Where(r => !string.IsNullOrEmpty(r.Field<String>("2")));
    if (twos.Any())
        row.SetField("2", twos.First().Field<String>("2"));
    var threes = lng.Where(r => !string.IsNullOrEmpty(r.Field<String>("3")));
    if (threes.Any())
        row.SetField("3", threes.First().Field<String>("3"));
}

answered Oct 16 '12 at 12:31

Tim Schmelter

450,073
74
686
939

thanks Tim. Works perfect. But if i dont know how many columns i have? fix are Name, LastName but 1,3,2,4,6,7... they are values from ID Column, which i made them ColumnName, what should the code would be? – Uni Le Oct 16 '12 at 12:42
Then the question will get unclear agin, what kind of DataTable has a unknown number of columns? Can you give an example? – Tim Schmelter Oct 16 '12 at 12:51
@UniLe: Please don't change your question so heavily. Now all answers should have been unaccepted/downvoted because they don't answer your current question anymore. Instead ask another question. – Tim Schmelter Oct 16 '12 at 13:17
Sry Tim, i mad a new thread: http://stackoverflow.com/questions/12916110/datatable-group-the-result-in-one-row – Uni Le Oct 16 '12 at 13:48

DataTable group the result

2 Answers2

Linked