Duplicates in different columns

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

General SQL Server Forums

New to SQL Server Programming

Duplicates in different columns

Author

Topic

grleenaw
Starting Member

1 Post

Posted - 2009-10-29 : 23:40:02

Hi all,

If anyone can help with this, I'd appreciate it.

We use SQL Server 2005 and I have inherited a table that has duplicates that have to be deleted.

The problem is that the duplicate records are not in the same column.

The table looks like this:


IdNr   CName    Tel1     Cd1        Tel2        Cd2 
1      A        1        2          3           4
12     A        3        4          1           2
35     B        5        6          7           9
36     C        5        6          7           9
37     A        2        4          8           6

In this table, a duplicate record has the same CName and the same set of Tel/Cd combinations, but it does not matter whether it is in Tel1/Cd1 or in Tel2/Cd2.

So, the rows with IdNr 1 and IdNr 12 are duplicates because they have the same CName (A) and they both have Tel/Cd combinations that are 1,2 and 3,4.

Is there a way to find duplicates that aren't all in the same column?

It does not matter which duplicate is deleted, just as long as one of the records stays.

Sample Code

create table #sample
(
IdNr	int primary key,
CName	varchar(1), 
Tel1	int, 
Cd1	int, 
Tel2	int, 
Cd2	int
)

insert into #sample 
select 1, 'A', 1, 2, 3, 4
union all
select 12, 'A', 3, 4, 1, 2
union all
select 35, 'B', 5, 6, 7, 9  
union all
select 36, 'C', 5, 6, 7, 9
union all 
select 37, 'A', 2, 4, 8, 6

Expected Result

select 12, 'A', 3, 4, 1, 2
union all
select 35, 'B', 5, 6, 7, 9  
union all
select 36, 'C', 5, 6, 7, 9
union all 
select 37, 'A', 2, 4, 8, 6

Thank you for reading this.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-10-30 : 02:27:46

Hi, Try this

DECLARE @TEMP TABLE ( ID INT IDENTITY(1,1) ,C1 VARCHAR(32),C2 VARCHAR(32), C3 VARCHAR(32) )
INSERT INTO @TEMP SELECT '1','2','3'
INSERT INTO @TEMP SELECT '3','1','2'
INSERT INTO @TEMP SELECT 'X','Y','Z'
INSERT INTO @TEMP SELECT 'Z','Y','X'
INSERT INTO @TEMP SELECT 'A','B','C'
INSERT INTO @TEMP SELECT 'C','B','A'
INSERT INTO @TEMP SELECT 'B','C','D'
INSERT INTO @TEMP SELECT 'C','B','D'
INSERT INTO @TEMP SELECT 'Z','Y','X'
INSERT INTO @TEMP SELECT 'Z','X','Y'
INSERT INTO @TEMP SELECT 'Y','Z','X'
INSERT INTO @TEMP SELECT 'Y','X','Z'
INSERT INTO @TEMP SELECT 'X','Y','Z'
INSERT INTO @TEMP SELECT 'X','Z','Y'

SELECT DISTINCT
MAX( CASE WHEN ROW = 1 THEN STRING ELSE NULL END ) AS C1,
MAX( CASE WHEN ROW = 2 THEN STRING ELSE NULL END ) AS C2,
MAX( CASE WHEN ROW = 3 THEN STRING ELSE NULL END ) AS C3
FROM
( SELECT ID, STRING, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY ID ) AS ROW
FROM ( SELECT DISTINCT ID, COLUMNSS AS STRING
FROM ( SELECT * FROM @TEMP ) P
UNPIVOT (COLUMNSS FOR [COLUMNS] IN ( C1,C2,C3 ) ) AS PT )A
) T
GROUP BY ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-31 : 01:49:36

[code]SELECT t.*
FROM table t
OUTER APPLY(SELECT COUNT(*) AS Cnt
FROM table
WHERE CName=t.CName
AND ((Tel1=c.Tel1 AND Cd1=c.Cd1 AND Tel2=c.Tel2 AND Cd2=t.Cd2)
OR (Tel1=c.Tel2 AND Cd1=c.Cd2 AND Tel2=c.Tel1 AND Cd2=t.Cd1))
AND IdNr > t.IdNr)t1
WHERE COALESCE(t1.Cnt,0)=0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources