Duplication Code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Duplication Code

Author

Topic

tress
Starting Member

3 Posts

Posted - 2011-04-28 : 03:46:18

Hi all,

Wondered if someone can help me as I have been trying to rework a piece of code that someone helped with writing to do something slightly different which was not in the original spec, this was posted on a different web site but unfortunately I have no access to this today and the results are needed urgently.

I am using SQL Server 2008 and I am trying to single out duplicate records within 1 table.

I have a table design with the following headers and example data, Tele is Telephone but I am just using a few numbers as an example of what I am trying to do.

ID Surname Postcode Tele1 Tele2 Tele3

1 Hughes CH5 0123 0123 0123

2 Matthews CH6 1234 1234 1234

3 Hughes CH5 NULL NULL 0123

4 Matthews CH6 NULL 1234 7896

5 James CH8 666 NULL NULL

What I need to show is if a record has the same surname, postcode and tele. number, no matter which one of the Tele columns it appears and not matter if it appears on another line with the same details, it counts as a duplicate record, hence Hughes and Matthews both have duplicate records so I only want to show the ID, Surname and Postcode.

Output would look like this based on the data from above.

ID Surname Postcode

1 Hughes CH5

2 Hughes CH5

3 Matthews CH6

4 Matthews CH6

I was helped to write the following code which only shows the duplicated record once and minus the ID but the person who asked for this originally is not the same person that wants it and things got lost in translation.

My current code looks like this

DECLARE @T TABLE (
Surname varchar(25),
Postcode varchar(10),
Tele1 varchar(15),
Tele2 varchar(15),
Tele3 varchar(15)
);

INSERT INTO @T(Surname, Postcode, Tele1, Tele2, Tele3)
SELECT 'Hughes', 'CH5', '0123', '0123', '0123' UNION ALL
SELECT 'Matthews', 'CH6', '1234', '1234', '1234' UNION ALL
SELECT 'Hughes', 'CH5', NULL, NULL, '0123' UNION ALL
SELECT 'Matthews', 'CH6', NULL, '1234', '7896' UNION ALL
SELECT 'James', 'CH8', '666', NULL, NULL;

WITH rs AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY Surname, Postcode) AS rn
FROM @T
)
SELECT DISTINCT
Surname, Postcode
FROM
rs
UNPIVOT
(
Tele_value
FOR Tele_Number IN ([Tele1], [Tele2], [Tele3])
) AS U
GROUP BY
Surname, Postcode, Tele_Value
HAVING
MIN(rn) <> MAX(rn);
GO

As you can see this does not include the change to show the ID field and doesn't show, the 2 lines that should appear for Hughes or Matthews

I hope this is enough just for someone to give me an idea about how to go about it.

Thanks in advance P

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-28 : 04:12:47

Not beautyful but it should work:


DECLARE @T TABLE (
ID int identity(1,1),
Surname varchar(25),
Postcode varchar(10),
Tele1 varchar(15), 
Tele2 varchar(15), 
Tele3 varchar(15)
);

INSERT INTO @T(Surname, Postcode, Tele1, Tele2, Tele3)
SELECT 'Hughes', 'CH5', '0123', '0123', '0123' UNION ALL
SELECT 'Matthews', 'CH6', '1234', '1234', '1234' UNION ALL
SELECT 'Hughes', 'CH5', NULL, NULL, '0123' UNION ALL
SELECT 'Matthews', 'CH6', NULL, '1234', '7896' UNION ALL
SELECT 'James', 'CH8', '666', NULL, NULL;

WITH rs AS (
SELECT *--, ROW_NUMBER() OVER(ORDER BY Surname, Postcode) AS rn
FROM @T
)
select distinct ID, Surname,Postcode from
(
select 
row_number() over (partition by Surname,Postcode order by Surname,Postcode) as rownum,
* from rs
UNPIVOT
(
Tele_value
FOR Tele_Number IN ([Tele1], [Tele2], [Tele3])
) AS U
)dt
where rownum > 1
order by Surname,Postcode,Id

No, you're never too old to Yak'n'Roll if you're too young to die.

tress
Starting Member

3 Posts

Posted - 2011-04-28 : 05:02:27

Hi webfred,

That worked a treat, I did try something similar but just couldn't get the desired results.

Thanks so much for the help. P

tress
Starting Member

3 Posts

Posted - 2011-04-28 : 11:21:10

Hi webfred,

Not sure if yourself or anyone will check this but I am now running the code against a larger data set and its bringing back records that are none duplicates, they dont have any other postcode's or phone numbers listed within the database that will define them as a duplicate, any ideas why this might happen?

Thanks Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources