Combining items in a given datatable column using LINQ

Question

I have a datatable which looks like this:

Id                                   | Title       | Month | Year |
ebdef240-abb7-4a82-9229-1ed37496da86 | Maths FT    | 1     | 2013 |
57504a66-4882-4794-a8b9-af0ead38dc70 | Maths FT    | 2     | 2013 |
57504a66-4882-4794-a8b9-af0ead38dc70 | Maths FT    | 2     | 2014 |
57504a66-4882-4794-a8b9-af0ead38dc70 | Maths FT    | 2     | 2015 |
ebdef239-abb7-4a82-9229-1ed37496da86 | English PT  | 1     | 2013 |
ebdef239-abb7-4a82-9229-1ed37496da86 | English PT  | 1     | 2014 |

but I would like it to be arranged like this:

Id                                   | Title       | Month | Years            |
ebdef240-abb7-4a82-9229-1ed37496da86 | Maths FT    | 1     | 2013             |
57504a66-4882-4794-a8b9-af0ead38dc70 | Maths FT    | 2     | 2013, 2014, 2015 |
ebdef239-abb7-4a82-9229-1ed37496da86 | English PT  | 1     | 2013, 2014       |

It maybe that it would make more sense to represent this as a list. I made an attempt at doing this, but am confused as to a) how I can combine the Years (as above, and b) include non-grouped fields, such as the ID (there are others, this is just a few of the columns for simplicity):

From LINQPad:

var objectTable = new DataTable();
objectTable.Columns.Add("Title",typeof(string));
objectTable.Columns.Add("id",typeof(Guid));
objectTable.Columns.Add("Month",typeof(int));
objectTable.Columns.Add("Year",typeof(string));
objectTable.Rows.Add("Maths FT", "ebdef240-abb7-4a82-9229-1ed37496da86", 1, "2013");
objectTable.Rows.Add("Maths FT", "57504a66-4882-4794-a8b9-af0ead38dc70", 2, "2013");
objectTable.Rows.Add("Maths FT", "57504a66-4882-4794-a8b9-af0ead38dc70", 2, "2014");
objectTable.Rows.Add("Maths FT", "57504a66-4882-4794-a8b9-af0ead38dc70", 2, "2015");
objectTable.Rows.Add("English PT", "ebdef239-abb7-4a82-9229-1ed37496da86", 1, "2013");
objectTable.Rows.Add("English PT", "ebdef239-abb7-4a82-9229-1ed37496da86", 1, "2014");

var DataSort = from row in objectTable.AsEnumerable()
            group row by new {title = row.Field<string>("Title"), month = row.Field<int>("Month")} into grp
            select new
                {
                    Title = grp.Key.title,
                    Month = grp.Key.month,                  
                };
DataSort.Dump();

Any examples would greatly appreciated.

Thanks.

possible duplicate of [Use LINQ to concatenate multiple rows into single row (CSV property)](http://stackoverflow.com/questions/614542/use-linq-to-concatenate-multiple-rows-into-single-row-csv-property) — mbeckish, May 07 '13 at 19:50

score 2 · Accepted Answer · answered May 07 '13 at 19:57

Perhaps:

var result = objectTable.AsEnumerable()
    .Select(r => new { Row = r, Title = r.Field<string>("Title"), Month = r.Field<int>("Month") })
    .GroupBy(x => new { x.Title, x.Month })
    .Select( g => new { 
        id = g.First().Row.Field<Guid>("id"), 
        g.Key.Title, 
        g.Key.Month, 
        Year = g.Select(x => x.Row.Field<string>("Year")).ToList()
    });

If you want a string with a comma separated list instead of the List<string> for the year-group use Year = string.Join(",", g.Select(x => x.Row.Field<string>("Year"))).

By the way, why is year a string instead of an int?

score 0 · Answer 2 · answered May 07 '13 at 19:54

0

This will be the LINQ statement for your output

from o in objectTable
group o by new { o.Id, o.Month, o.Title } into g
select new {Id = g.Key.Id, Title = g.Key.Id, Month = g.Key.Month, Years= String.Join(" ", g.Select(x=>x.Year).ToArray()) };

answered May 07 '13 at 19:54

arunlalam

1,838
2
15
23

Combining items in a given datatable column using LINQ

2 Answers2