Checking for duplicates in several 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

SQL Server 2005 Forums

Transact-SQL (2005)

Checking for duplicates in several columns

Author

Topic

oldfox
Starting Member

17 Posts

Posted - 2010-07-06 : 13:19:01

Hi all, I have a routing table with ~25 columns Passenger | City1 | City2 |...| City25

Each passenger could have from 1 to 25 cities, but cities have to be unique, so I have to check that passenger does not have the same city twice in the route.
How to check that each city appears only once in all 25 columns?
Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 07:52:40

[code]-- creating testdata with 5 Cities
declare @test table (Passenger varchar(30),
City1 varchar(30),
City2 varchar(30),
City3 varchar(30),
City4 varchar(30),
City5 varchar(30))

insert @test
select 'Ballack', 'Hamburg', 'New York', 'Paris', 'Chicago', 'Stockholm' union all
select 'Klose' , 'Kairo' , 'Berlin' , 'Lima' , 'Bern' , 'Berlin'

-- Solution
select Passenger, dtPivot.Value as City, count(*) as Occurrence
from
(select Passenger, City1, City2, City3, City4, City5 from @test) dt1
UNPIVOT(
Value For Property in (City1, City2, City3, City4, City5)) dtPivot
group by Passenger, dtPivot.Value
having count(*) > 1[/code]

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-07-07 : 08:11:31

a PERFECT example of why you should normalize your data

Hey Fred, are you guys going to go to the finals for the cup?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 09:10:56

quote:
Originally posted by DonAtWork
Hey Fred, are you guys going to go to the finals for the cup?

I hope so but I am really not sure because Spain is a very strong team.
Anyway - for me our "boy group"-members are heroes right now!

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-07 : 10:51:31

Euro 2008 :(

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources