471,091 Members | 1,465 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,091 software developers and data experts.

Merging Duplicate Rows

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

Jul 23 '05 #1
5 9092
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)
Jul 23 '05 #2
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
--

Jul 23 '05 #3

"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
Jul 23 '05 #4
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

Jul 23 '05 #5
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
--

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Klatuu | last post: by
2 posts views Thread by Emmett Power | last post: by
12 posts views Thread by google_groups3 | last post: by
3 posts views Thread by Ralph Smith | last post: by
2 posts views Thread by Neil Chambers | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.