I am trying to compare two addresses from the same ID to see whether they match. For example:

Id  Adress Code     Address
1   1               123 Main
1   2               123 Main
2   1               456 Wall
2   2               456 Wall
3   1               789 Right
3   2               100 Left

I'm just trying to figure out which column have changed value between two records having same ID. Like in above data ID ( 1, 2 ) have same address but different address code and ID 3 have different Address

Update Expected result should be like this

Id   ColumnChanged
1    AddressCode
2    AddressCode
3    Address
  • 1
    In order to better understand. Can you give to us the expected result from the data you supply?
    – JGA
    Dec 14, 2014 at 20:31
  • And if two records from the same id differ in more than one column?
    – JGA
    Dec 14, 2014 at 20:44
  • If fact from your sample data for id = 3 the value of Columns Changed could be AddessCode too.
    – JGA
    Dec 14, 2014 at 20:54

1 Answer 1


This is the solution I propose, If you have more columns to test differences you just have to union another select in the same manner. As I previously commented, in the sample date you provide for id =3 both [Address] and [Address Code] have different values, for these situations two rows are given one showing that column [Address] is different and another showing that column [Address Code] is different.

select id, 'Address Code' As [Column Changed] from
select distinct id, [Address Code] from Addresses 
) As Differents
group by id
Having count(*) > 1
select id, 'Address'  As [Column Changed] from
select distinct id, [Address] from Addresses 
) As Differents
group by id
Having count(*) > 1


id  Column Changed
1   Address Code
2   Address Code
3   Address Code
3   Address

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.