Merging Duplicate Rows with SQL-CodePudding

I have a table that contains usernames, these names are duplicated in various forms, for example, Mr. John is replicated as John Mr. I want to combine the two rows using their unique phone numbers in SQL.

I want a new table in this form after removing the duplicates

CodePudding user response：

you can do it with ROW_NUMBER window function.

First, you need to group the data by your unique column (Phone_Number), then sort by name.

Preparing the table and example data:

DECLARE @vCustomers TABLE (
    Name            NVARCHAR(25),
    Phone_Number    NVARCHAR(9),
    Address         NVARCHAR(25)
)

INSERT INTO @vCustomers
VALUES
    ('Mr John', '234881675', 'Lagos'),
    ('Mr Felix', '234867467', 'Atlanta'),
    ('Mrs Ayo', '234786959', 'Doha'),
    ('John Mr', '234881675', 'Lagos'),
    ('Mr Jude', '235689760', 'Rabat'),
    ('Ayo', '234786959', 'Doha'),
    ('Jude', '235689760', 'Rabat')

After that, removing the duplicate rows:

DELETE
    vc
FROM (
    SELECT
    ROW_NUMBER() OVER(PARTITION BY Phone_Number ORDER BY Name DESC) AS RN
    FROM @vCustomers
) AS vc
WHERE RN > 1


SELECT * FROM @vCustomers

As final, the result:

Name	Phone_Number	Address
Mr John	234881675	Lagos
Mr Felix	234867467	Atlanta
Mrs Ayo	234786959	Doha
Mr Jude	235689760	Rabat