CODEWITHSUNDEEP
c#datatable
WSBT
WSBT

Reputation: 36333

How to merge DataTables but combine rows if ID matches?

Input:

Say I have two DataTables. The first one:

ID  Date    ValueA  ValueB
1   Nov.21  72.4    23.4
1   Nov.22  71.4    24.4
2   Nov.21  74.4    21.4

The second one:

ID  Date    ValueC  ValueD
1   Nov.21  42.4    53.4
1   Nov.22  41.4    54.4
2   Nov.21  44.4    51.4

What I tried:

I tried to do DataTable1.Merge(DataTable2);, but I get this:

ID  Date    ValueA  ValueB  ValueC  ValueD
1   Nov.21  72.4    23.4    
1   Nov.22  71.4    24.4    
2   Nov.21  74.4    21.4    
1   Nov.21                  42.4    53.4
1   Nov.22                  41.4    54.4
2   Nov.21                  44.4    51.4

Desired result:

I'd like to combine rows, if their ID and Date match.

I want this:

ID  Date    ValueA  ValueB  ValueC  ValueD
1   Nov.21  72.4    23.4    42.4    53.4
1   Nov.22  71.4    24.4    41.4    54.4
2   Nov.21  74.4    21.4    44.4    51.4

Upvotes: 2

Views: 4281

Answers (3)

Biondo86
Biondo86

Reputation: 903

The above solution is missing a method to fill the datatable. it should be

DataTable dtResult = new DataTable();

var query = (from row1 in dt1.AsEnumerable()
            join row2 in dt2.AsEnumerable() on
                new { ID = row1.Field<int>("ID"), Date = row1.Field<DateTime>("Date") }
                equals
                new { ID = row2.Field<int>("ID"), Date = row2.Field<DateTime>("Date") }
            select dtResult.LoadDataRow(new object[]
    {
        row1.Field<int>("ID"),
        row1.Field<DateTime>("Date"),
        row1.Field<double>("ValueA"),
        row1.Field<double>("ValueB"),
        row2.Field<double>("ValueC"),
        row2.Field<double>("ValueD")
    }, false))
.CopyToDataTable();

the .CopyToDataTable(); is the magic keyword

Upvotes: 0

Steve Lillis
Steve Lillis

Reputation: 3256

You can use LINQ to group by, then put the values back into a DataTable if you need it that way:

var rows = dt.AsEnumerable()
                    .Select(s =>
                        new
                        {
                            ID = s.Field<int>("ID"),
                            Date = s.Field<DateTime>("Date"),
                            ValueA = s.Field<int>("ValueA")
                        })
                    .GroupBy(
                        key => new { key.ID, key.Date },
                        (key, agg) =>
                        new
                        {
                            key.ID,
                            key.Date,
                            ValueA = agg.Max(a => a.ValueA) // etc.
                        });

        var grouped = new DataTable("grouped");

        foreach (var r in rows)
        {
            var row = grouped.NewRow();

            // Put the rows that are now as you want them back into a datatable

            grouped.Rows.Add(row);
        }

Upvotes: 1

Habib
Habib

Reputation: 223277

You can use LINQ to join the two DataTables based on ID and Date and then project/Load the results into a resultant DataTable using LoadDataRow method like:

DataTable dtResult = new DataTable();

var query = from row1 in dt1.AsEnumerable()
            join row2 in dt2.AsEnumerable() on
                new { ID = row1.Field<int>("ID"), Date = row1.Field<DateTime>("Date") }
                equals
                new { ID = row2.Field<int>("ID"), Date = row2.Field<DateTime>("Date") }
            select dtResult.LoadDataRow(new object[]
    {
        row1.Field<int>("ID"),
        row1.Field<DateTime>("Date"),
        row1.Field<double>("ValueA"),
        row1.Field<double>("ValueB"),
        row2.Field<double>("ValueC"),
        row2.Field<double>("ValueD")
    }, false);

Make sure that the types specified with Field extension method matches with that of your columns in DataTable.

Upvotes: 3

Related Questions