Merge records in SQL SERVER

Question

I need to normalize a table that contains anagraphic data. Here is a sample table with some rows:

Id LastName FirsName Address    Email       Tel     Mobile Age
1  Rossi    Mario    Via Milano NULL        123456  NULL   41
2  Rossi    Mario    NULL       rm@test.it  123456  NULL   NULL
3  Rossi    Mario    Via Milano NULL        NULL    254521 NULL

I want merge the values in the records in order to have one unique record with all values. Like this:

Id LastName FirsName Address    Email       Tel     Mobile Age
1  Rossi    Mario    Via Milano rm@test.it  123456  254521 41

I tried to use the MERGE statement, but I don't think this is the right solution.

Any help is appreciated.

Answer 1

You're looking at a two step operation. Either insert the merged data into a new table then drop the original table or update one row with the merged data and delete all the extra rows. At first glance, the former method seems easiest by far.

insert into NewTable( ID, LastName, FirstName, Address, Email, ...
    select  Min( ID ), LastName, FirstName, Min( Address ), Min( Email ), ...
from   OldTable
group by LastName, FirstName;

drop   OldTable;

exec sp_rename 'NewTable', 'OldTable'; -- Optional

But you may not have the privileges to drop and rename tables. Plus you have to make sure you duplicate any triggers, indexes, constraints, etc.

The latter method is a little trickier DML-wise, but safer.

with
New as(
    select  Min( ID ) MinID, LastName, FirstName,
            Min( Address ) MinAddr, Min( Email ) MinEmail, 
            Min( Tel ) MinTel, Min( Mobile ) MinMobil, Min( Age ) MinAge
    from    OldTable
    group by LastName, FirstName
)
update  Old
    set Old.Address = New.MinAddr,
        Old.Email   = New.MinEmail,
        Old.Tel     = New.MinTel,
        Old.Mobile  = New.MinMobil,
        Old.Age     = New.MinAge
from    OldTable    Old
join    New
    on  New.MinID = Old.ID;

select * from OldTable;

with
MinIDs as(
    select  Min( ID ) MinID, LastName, FirstName
    from    OldTable
    group by LastName, FirstName
)
delete from Old
from    OldTable Old
join    MinIDs    MI
    on  MI.MinID < Old.ID;

And it really doesn't matter if you use Min or Max with the non-key fields. Here's the Fiddle for the latter method.

Answer 2

If you are considering FirstName and LastName as key identifiers then you can write as:

SELECT MIN(Id),     
       T1.LastName ,
       T1.FirsName,       
        SUBSTRING ((SELECT DISTINCT  CASE WHEN T2.Address IS NULL
        THEN '' ELSE ','+ T2.Address END  
        FROM @Test T2 
        WHERE T1.LastName = T2.LastName AND T1.FirsName = T2.FirsName
        FOR XML PATH('')),2,8000) AS [Address],
        SUBSTRING ((SELECT DISTINCT  CASE WHEN T3.Email  IS NULL 
        THEN '' ELSE ','+ T3.Email END
        FROM @Test T3 
        WHERE T1.LastName = T3.LastName AND T1.FirsName = T3.FirsName
    FOR XML PATH('')),2,8000)AS Email
FROM @Test T1
GROUP BY T1.LastName ,T1.FirsName

Answer 3

I think this might helps you:-

UPDATE TAB_NAME
SET ID = MIN(ID), LastName = MAX(LastName), FirsName = MAX(FirsName)
    Address = MAX(Address), Email = MAX(Email), Tel = MAX(Tel), 
    Mobile = MAX(Mobile), Age = MAX(Age)
WHERE COND.;