Merge duplicate row in one with other column in c#

Question

I have a DataTable that contain duplicate row with difference value in one column. like this:

So, I want merge duplicate PersonId in one row where PersonId and OrginalDate and ConvertedDate is same. like this:

It is possible that one person has even more records in one date

Update:

Note: I don't want delete duplicate records. If you pay close attention to the photos, I want to merge duplicate records into another table with more columns

@Filburt The second table is different from the first table. I want to merge the records of the first table into the second table. Just like second image — Sina, Jul 18 '23 at 12:05
Does this answer your question? [How to select distinct rows in a datatable and store into an array](https://stackoverflow.com/questions/1199176/how-to-select-distinct-rows-in-a-datatable-and-store-into-an-array) — Filburt, Jul 18 '23 at 12:07
@Filburt Unfortunately not. I explained more in the question — Sina, Jul 18 '23 at 13:54

score 0 · Answer 1 · answered Jul 18 '23 at 12:56

Try following :

       static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Personid", typeof(int));
            dt.Columns.Add("OrginalDate", typeof(int));
            dt.Columns.Add("ConvertedDate", typeof(DateTime));
            dt.ColumnsAdd("OriginalTime", typeof(int));
            dt.Columns.Add("ConvertedTime", typeof(TimeSpan));

            dt.Rows.Add(new object[] { 115, 45099, DateTime.Parse("6/22/2023 12:00:00"), 1206, TimeSpan.Parse("12:06") });
            dt.Rows.Add(new object[] { 115, 45099, DateTime.Parse("6/22/2023 12:00:00"), 1859, TimeSpan.Parse("18:59") });
            dt.Rows.Add(new object[] { 631, 45099, DateTime.Parse("6/22/2023 12:00:00"), 1449, TimeSpan.Parse("14:49") });
            dt.Rows.Add(new object[] { 631, 45099, DateTime.Parse("6/22/2023 12:00:00"), 1722, TimeSpan.Parse("17:22") });
            dt.Rows.Add(new object[] { 631, 45099, DateTime.Parse("6/22/2023 12:00:00"), 1819, TimeSpan.Parse("18:19") });
            dt.Rows.Add(new object[] { 631, 45099, DateTime.Parse("6/22/2023 12:00:00"), 2006, TimeSpan.Parse("20:06") });

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<int>("Personid"));

            var maxItems = groups.Max(x => x.Count()) / 2;

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Personid", typeof(int));
            pivot.Columns.Add("OrginalDate", typeof(int));
            pivot.Columns.Add("ConvertedDate", typeof(DateTime));

            for (int i = 1; i <= maxItems; i++)
            {
                pivot.Columns.Add("EnterTime" + i, typeof(TimeSpan));
                pivot.Columns.Add("ExitTime" + i, typeof(TimeSpan));
            }

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["Personid"] = group.First()["Personid"];
                newRow["OrginalDate"] = group.First()["OrginalDate"];
                newRow["ConvertedDate"] = group.First()["ConvertedDate"];
                for(int i = 0; i < group.Count() / 2; i++)
                {
                    newRow["EnterTime" + (i + 1)] = group.Skip(2 * i).First().Field<TimeSpan>("ConvertedTime");
                    newRow["ExitTime" + (i + 1)] = group.Skip((2 *i) + 1).First().Field<TimeSpan>("ConvertedTime");
                }
            }


        }

Results

Your code looks correct, but the ‍‍‍```maxItems``` value is not calculated correctly in the main table. It should be 4 or 6 in the end, which calculates 32, can you help me fix it? This piece of code is also wrong: ‍‍‍ ```for (int i = 0; i < group.Count() / 2; i++) { newRow["EnterTime" + (i + 1)] = group.Skip(2 * i).First().Field("ConvertedTime"); newRow["ExitTime" + (i + 1)] = group.Skip((2 * i) + 1).First().Field("ConvertedTime"); }``` — Sina, Jul 19 '23 at 04:52
The max value is divided by 2 and is the max of any grouped item. The calculations are easy if you divided the count by 2. I then multiply the count by 2 to get the even rows (enter time) and odd rows (exit time). The code gets the correct values you posted in the question. You posted answer may be wrong. Applications like this usually the exit time of one row is the enter time of next row. Your answer the input alternates between one row being the enter time and next row the exit time. — jdweng, Jul 19 '23 at 09:17

score 0 · Accepted Answer · answered Jul 19 '23 at 06:20

I found my answer

var groups = dataTable.AsEnumerable().GroupBy(row => new
 {
   PersonId = row.Field<double>("PersonId"),
   OrginalDate = row.Field<double>("OrginalDate")
 });

var maxItems = groups.Max(x => x.Count()) / 2;
DataTable pivot= new DataTable();
pivot.Columns.Add("PersonId", typeof(int));
pivot.Columns.Add("OrginalDate", typeof(int));
pivot.Columns.Add("GregorianConvertedDate", typeof(DateTime));
pivot.Columns.Add("PersianConvertedDate", typeof(string));

for (int i = 1; i <= maxItems; i++)
{
  pivot.Columns.Add("EnterTime" + i, typeof(TimeSpan));
  pivot.Columns.Add("ExitTime" + i, typeof(TimeSpan));
}

foreach (var group in groups)
{
  DataRow newRow = tempDataTable.Rows.Add();
  newRow["PersonId"] = group.First()["PersonId"];
  newRow["OrginalDate"] = group.First()["OrginalDate"];
  newRow["GregorianConvertedDate"] = group.First()["GregorianConvertedDate"];
  newRow["PersianConvertedDate"] = group.First()["PersianConvertedDate"];

  for (int i = 0; i < group.Count() / 2; i++)
  {
    newRow["EnterTime" + (i + 1)] = group.Skip(2 * i).First().Field<string>("ConvertedTime");
    newRow["ExitTime" + (i + 1)] = group.Skip((2 * i) + 1).First().Field<string>("ConvertedTime");
   }
}

Many thanks to @jdweng

Merge duplicate row in one with other column in c#

2 Answers2