CODEWITHSUNDEEP
sqlsql-server
Mediator Mouth
Mediator Mouth

Reputation: 21

Duplicate records - query to show both keys in one row

After merging two databases, I have duplicate customer records, which I selected and copied to a table that looks like this:

ID     Address
a1     101 Apple Way
a2     101 Apple Way
b1     102 Banana Place
b2     102 Banana Place
c1     103 Chocolate River Dr.
c2     103 Chocolate River Dr.

I would like to produce a table that looks like this:

ID1    ID2    Address
a1     a2     101 Apple Way
a2     a1     101 Apple Way
b1     b2     102 Banana Place
b2     b1     102 Banana Place
c1     c2     103 Chocolate River Dr.
c2     c1     103 Chocolate River Dr.

After some more manipulation, I will feed the second table to a procedure which will combine the two customer records and insure updates cascade.

What SQL query can I use to create my second table? Thanks for taking a look!

Upvotes: 2

Views: 42

Answers (1)

Dhaval
Dhaval

Reputation: 2379

Try this....

select t2.id as ID1,t1.ID  as ID2,t1.Address from tt t1
inner join tt as t2 on t1.Address=t2.Address and  t2.ID<>t1.ID

DEMO FIDDLE

Upvotes: 1

Related Questions