CODEWITHSUNDEEP
c#.netdatatable
Zack09
Zack09

Reputation: 103

how to merge rows with similar data

I have a datatable with 3 columns ( first name, last name, age), I wanna browse through it and look for the rows that have the same first and last name, and replace them with one row that'll have the same first and last name. As for the age column I wanna put all the ages in one cell and seperate them with a '|' or ';'.

Before:

John # Doe # 24

John # Doe # 35

After:

John # Doe # 24|35

Upvotes: 0

Views: 1139

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131492

You can use LINQ and the extensions in the System.Data.DataSetExtensions.dll assembly like DataRowExtensions to group the rows and join the values you want like this:

        var dt=new DataTable();
        dt.Columns.Add("A", typeof (string));
        dt.Columns.Add("B", typeof (string));
        dt.Columns.Add("C", typeof (int));

        dt.Rows.Add("John", "Doe", 23);
        dt.Rows.Add("John", "Doe", 24);
        dt.Rows.Add("John", "Doe", 25);

        var result = from row in dt.AsEnumerable()
                     group row by new {A=row.Field<string>("A"), B=row.Field<string>("B")} into grp
                     select new
                     {
                         CustomerName = grp.Key,
                         Items = String.Join(",",grp.Select(r=>r.Field<int>("C")))
                     };
        foreach (var t in result)
            Console.WriteLine(t.CustomerName + " " + t.Items);

The trick is to realize that what you ask is how to group the data and then aggregate the value column using something other than Count() or Sum()

You'll find many questions in SO that ask how to group DataTable rows, eg. this one that you can use as a reference to make more complex groupings

Upvotes: 2

Related Questions