Merging Duplicate Rows

[hharry]

Hello All,

I have an issue with dupliate Contact data. Here it is:

I have a Contacts table;

CREATE TABLE CONTACTS
(
SSN int,
fname varchar(40),
lname varchar(40),
address varchar(40),
city varchar(40),
state varchar(2),
zip int
)

Here is some sample data:

SSN: 1112223333
FNAME: FRANK
LNAME: WHALEY
ADDRESS: NULL
CITY: NULL
STATE NY
ZIP 10033

SSN: 1112223333
FNAME: NULL
LNAME: WHALEY
ADDRESS: 100 MADISON AVE
CITY: NEW YORK
STATE NY
ZIP NULL

How do I merge the 2 rows to create one row as follows:
via SQL or T-SQL

SSN: 1112223333
FNAME: FRANK
LNAME: WHALEY
ADDRESS: 100 MADISON AVE
CITY: NEW YORK
STATE NY
ZIP 10033

Pointers appreciated.
Thanks

[Hugo Kornelis]

On 16 Feb 2005 13:18:24 -0800, hharry wrote:

I have an issue with dupliate Contact data. Here it is: (snip)How do I merge the 2 rows to create one row as follows:
via SQL or T-SQL

(snip)

Hi hharry,

The following is untested, since you didn't include the INSERT statements
to recreate your data on my system, but I believe it should work:

SELECT SSN, MAX(Fname) AS Fname), MAX(LName) AS LName,
MAX(Address) AS Address, MAX(City) AS City,
MAX(State) AS State, MAX(ZIP) AS ZIP
FROM Contacts
GROUP BY SSN

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

One very naive method would be the following:

INSERT INTO NewTable (ssn, fname, lname, address, city, state, zip)
SELECT ssn, MAX(fname), MAX(lname),
MAX(address), MAX(city), MAX(state), MAX(zip)
FROM Contacts
GROUP BY ssn

but it really depends on what rules you want for the data cleansing.
For example, what result would you want if Frank moved from New York to
Seattle but the WA state code wasn't entered? The above query would
return an invalid address: Seattle, NY. This is where specialist
address validation software is useful.

--
David Portas
SQL Server MVP
--

[Simon Hayes]

"hharry" <pa*********@nyc.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

Hello All,

I have an issue with dupliate Contact data. Here it is:

I have a Contacts table;

CREATE TABLE CONTACTS
(
SSN int,
fname varchar(40),
lname varchar(40),
address varchar(40),
city varchar(40),
state varchar(2),
zip int
)

Here is some sample data:

SSN: 1112223333
FNAME: FRANK
LNAME: WHALEY
ADDRESS: NULL
CITY: NULL
STATE NY
ZIP 10033

SSN: 1112223333
FNAME: NULL
LNAME: WHALEY
ADDRESS: 100 MADISON AVE
CITY: NEW YORK
STATE NY
ZIP NULL

How do I merge the 2 rows to create one row as follows:
via SQL or T-SQL

SSN: 1112223333
FNAME: FRANK
LNAME: WHALEY
ADDRESS: 100 MADISON AVE
CITY: NEW YORK
STATE NY
ZIP 10033

Pointers appreciated.
Thanks

Based on your sample data, your table has no primary key, or it would not be
possible to have multiple rows with a single SSN. I strongly suggest you add
a primary key to correct your data model and prevent these duplicates,
otherwise you will continue to get bad data in your table. As a quick fix,
you may be able to do something like this:

update dbo.Contacts
set fname = coalesce(c1.fname, c2.fname),
lname = coalesce(c1.lname, c2.lname),
address = coalesce(c1.address, c2.address)
/* etc */
from dbo.Contacts c1
join dbo.Contacts c2
on c1.SSN = c2.SSN

But this assumes that you have only two rows (maximum) per SSN, and there
are no cases where there are different fname or lname values for each SSN,
only one with a NULL and one without. What do you do if one row has fname
'Steven' and another one has fname 'Stephen' with the same SSN? There is no
way to decide automatically which is correct - you have to find out from
some other source.

Simon

[hharry]

Thanks All,

I will add some clarification. I have a base Contacts table and receive
updates in flat file format as the base table data is incomplete. Now,
I planned on loading the flat file data into a temp table with the same
structure as the base table and then updating incomplete base table
rows with values from the temp table, matching on SSN.

For example, Frank Whaley has missing addess information which is
located in the temp table...how do i update the base with the temp
data, assuming I don't know in advance which column data is incomplete
?

Thanks

[David Portas]

So you now have two tables instead of one. Can I assume that SSN is the
key in both? Please include DDL with future posts so that we don't have
to guess these things. Try this:

UPDATE C
SET fname = COALESCE(C.fname, C2.fname),
lname = COALESCE(C.lname, C2.lname),
address = COALESCE(C.address, C2.address),
city = COALESCE(C.city, C2.city),
state = COALESCE(C.state, C2.state),
zip = COALESCE(C.zip, C2.zip)
FROM Contacts AS C
JOIN Contacts2 AS C2
ON C.ssn = C2.ssn

--
David Portas
SQL Server MVP
--