Is there an algorithm or pattern to merge several rows of the same record into one row?

Question

Due to some unknown fault, every time I have sync'ed my Nokia's Contacts with my Outlook Contacts, via Nokia Suite, each contact on the phone gets added to Outlook again. I now have up to four copies of some contacts in Outlook. Some have different fields populated in different duplicates.

What I want to do is import my contacts into an a database table or in-memory object collection, from CSV, and then merge the properties of all copies of each 'unique' record into one record, and import back into an empty Contacts folder in outlook. Is there any elegant way to do this, either in plain C#, LINQ, or T-SQL?

Or do I just loop through all copies (rows) of the first column, and copy any values found into versions of that column that are blank or less up to date, then carry on iterating onto the second column through to the last?

score 1 · Answer 1 · answered Apr 27 '13 at 11:42

My strategy would be to first group all rows on some key like new { FirstName, LastName } or EMail (I don't know what your data looks like). Now you have groups of rows that all belong to the same person. You now need to merge them (using any algorithm you like). You could either choose the newest one, or merge individual attributes like this:

from r in rows
group r by r.EMail into g
select new {
 EMail = g.Key,
 DateOfBirth = g.Select(x => x.DateOfBirth).Where(x => x != null).First(),
 ...
}

In this example I'm picking the first non-null value for DateOfBirth non-deterministically.

Is there an algorithm or pattern to merge several rows of the same record into one row?

1 Answers1