merge duplicate rows in DataTable in one record and updating some columns

Question

I have a Datatable in my C# code

DataTable dtDetails;

My Datatable how following records;

id | tid | code | pNameLocal        | qty | price
-------------------------------------------------
1  |101  | 101  | some_local_name   | 2   |20.36
2  |102  | 202  | some_local_name   | 1   |15.30 // exactly same entry 
3  |102  | 202  | some_local_name   | 1   |15.30 // exactly same entry
4  |102  | 202  | some_local_name   | 1   |10.00 //same entry as same tid but price is different
5  |102  | 202  | some_local_name   | 2   |15.30 //same entry as same tid but different qty
6  |102  | 202  | some_local_name2  | 1   |15.30 //same entry as same tid but pNameLocal different
7  |103  | 202  | some_local_name   | 1   |15.30 // different entry of same product see different tid
8  |104  | 65   | some_local_name   | 5   |05.00
9  |105  | 700  | some_local_name   | 2   |07.01 // does not exist in "dtProduct"

what to do is records which are exactly same but entered multiple times to be merged into one, but keeping their qty column and price column updated, for example in above DaraRows id 2 and 3 have exactly same record as id 1, this should be one record and update qty and price by adding from duplicate records which are exactly same. this should update in same DataTable or in new.

Answer 1

You can take the DataTable as enumarable, and use Linq to create your new DataTable. The code would look something like this:

    DataTable newDt = dt.AsEnumerable()
        .GroupBy(r => r.Field<int>("tid"))
        .Select(g => {
            var row = dt.NewRow();

            row["tid"] = g.Key;
            row["code"] = g.First(r => r["code"] != null).Field<int>("code");
            row["pNameLocal"] = g.First(r => r["pNameLocal"] != null).Field<string>("pNameLocal");
            row["qty"] = g.Sum(r => r.Field<int>("qty"));
            row["price"] = g.Sum(r => r.Field<double>("price"));

            return row;
        }).CopyToDataTable();

See sample console application below, setting up sample data, executing the grouping and displaying the original and result Datatables.

using System;
using System.Data;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
    static void Main(string[] args)
    {
        var dt = FillUpTestTable();
        DumpDataTable(dt);
        DataTable newDt = dt.AsEnumerable()
            .GroupBy(r => r.Field<int>("tid"))
            .Select(g => {
                var row = dt.NewRow();

                row["tid"] = g.Key;
                row["code"] = g.First(r => r["code"] != null).Field<int>("code");
                row["pNameLocal"] = g.First(r => r["pNameLocal"] != null).Field<string>("pNameLocal");
                row["qty"] = g.Sum(r => r.Field<int>("qty"));
                row["price"] = g.Sum(r => r.Field<double>("price"));

                return row;
            }).CopyToDataTable();

        Console.WriteLine();
        Console.WriteLine("Result: ");
        Console.WriteLine();

        DumpDataTable(newDt);
        Console.ReadLine();
    }

    private static DataTable FillUpTestTable()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("id", typeof(int));
        dt.Columns.Add("tid", typeof(int));
        dt.Columns.Add("code", typeof(int));
        dt.Columns.Add("pNameLocal", typeof(string));
        dt.Columns.Add("qty", typeof(int));
        dt.Columns.Add("price", typeof(double));

        dt.Rows.Add(1, 101, 101, "some_local_name", 2, 20.36);
        dt.Rows.Add(2, 102, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(3, 102, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(4, 102, 202, "some_local_name", 2, 10.00);
        dt.Rows.Add(5, 102, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(6, 102, 202, "some_local_name2", 1, 15.30);
        dt.Rows.Add(7, 103, 202, "some_local_name", 2, 15.30);
        dt.Rows.Add(8, 104, 202, "some_local_name", 2, 05.00);
        dt.Rows.Add(9, 105, 202, "some_local_name", 2, 07.01);

        return dt;
    }
    private static void DumpDataTable(DataTable newDt)
    {
        foreach (DataRow dataRow in newDt.Rows)
        {
            foreach (var item in dataRow.ItemArray)
            {
                Console.Write(item + "   | ");
            }
            Console.WriteLine();
        }
    }
}

}

Answer 2

You can use GroupBy on an anonymous type containing all columns apart from count and price:

var aggregatedRowData = dtDetails.AsEnumerable()
    .Select(r => new
    {
        tid = r.Field<int>("tid"),
        code = r.Field<int>("code"),
        pNameLocal = r.Field<string>("pNameLocal"),
        qty = r.Field<int>("qty"),
        price = r.Field<decimal>("price"),
    })
    .GroupBy(x => new { x.tid, x.code, x.pNameLocal })
    .Select(grp => new
    {
        grp.Key.tid,
        grp.Key.code,
        grp.Key.pNameLocal,
        qty = grp.Sum(x => x.qty),
        price = grp.Sum(x => x.price)
    });

DataTable aggregatedTable = dtDetails.Clone();  // empty, same columns
foreach (var x in aggregatedRowData)
    aggregatedTable.Rows.Add(x.tid, x.code, x.pNameLocal, x.qty, x.price);

Answer 3

You need like this in datatable:

SELECT tid, code, pNameLocal, sum(qty),sum(price) FROM @tbl
group by tid, code, pNameLocal,qty, price

You can use linq. Try following code. I tried to use your sample records.

 DataTable dt = new DataTable();

            DataColumn[] dcCol =  {
                new DataColumn("id",typeof(int)),
                new DataColumn("tid", typeof(int)),
                new DataColumn("code", typeof(int)),
                new DataColumn("pNameLocal", typeof(string)),
                new DataColumn("qty", typeof(int)),
                new DataColumn("price", typeof(decimal))
        };

            dt.Columns.AddRange(dcCol);


            dt.Rows.Add(1, 101, 101, "some_local_name", 2, 20.36);
            dt.Rows.Add(2, 102, 202, "some_local_name", 1, 10.00);
            dt.Rows.Add(3, 102, 202, "some_local_name", 1, 15.30);
            dt.Rows.Add(4, 102, 202, "some_local_name", 1, 10.00);
            dt.Rows.Add(5, 102, 202, "some_local_name", 2, 15.30);
            dt.Rows.Add(6, 102, 202, "some_local_name2", 1, 15.30);
            dt.Rows.Add(7, 103, 202, "some_local_name", 1, 15.30);
            dt.Rows.Add(8, 104, 65, "some_local_name", 5, 05.00);
            dt.Rows.Add(9, 105, 700, "some_local_name", 2, 07.01);


            var dtnew = from r in dt.AsEnumerable()
                        group r by new
                        {
                            tid = r.Field<int>("tid"),
                            code = r.Field<int>("code"),
                            pNameLocal = r.Field<string>("pNameLocal"),
                            qty = r.Field<int>("qty"),
                            price = r.Field<decimal>("price")
                        } into grp
                        select new
                        {
                            tid1 = grp.Key.tid,
                            code1 = grp.Key.code,
                            pNameLocal1 = grp.Key.pNameLocal,
                            SumQty = grp.Sum(r => grp.Key.qty),
                            sumPrice = grp.Sum(r => grp.Key.price)
                        };

Answer 4

This is what you can do If rows are to be distinct based on ALL COLUMNS.

DataTable newDatatable = dt.DefaultView.ToTable(true, "tid", "code", "pNameLocal", "qty", "price");

The columns you mention here, only those will be returned back in newDatatable.