Sql-server – How to know which columns value is different between two rows

sql serversql-server-2008

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

Best Answer

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
UNION 
select id, 'Address'  As [Column Changed] from
(
select distinct id, [Address] from Addresses 
) As Differents
group by id
Having count(*) > 1

Output

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

Related Solutions

Sql-server – Doing a comparison on two csv files with no Primary Key

If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by

SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;

Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, given the lack of a key on the staging table (and hopefully not on the real one - it's not clear from your question), deleting rows based on this comparison can be painful, especially with so many rows.

You can drop the staging table once you have finished.

(As far as I see, this approach will work in any RDBMS.)

SQL Server – How to Order by Custom Filter Excluding Certain Data

This will get your result set, though I did add the Name field for clarity. The common table expression (cte) with ROW_NUMBER() will get the email priority. Then I left joined that back to the base table to return the names and a NULL for Julius.

CREATE TABLE #emailOrPhone (ID INT IDENTITY(1,1), Type INT, Number VARCHAR(50), Name VARCHAR(20))

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1213','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1312','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1012,'sally@ourdomain.com','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'sallyf@gmail.com','Sally')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'frankiebaby@yahoo.com','Frank')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'franks@ibm.com','Frank')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1010,'555-123-9876','Julius')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'425-369-1470','Julius')

;WITH cte AS
(
SELECT Name, Number
, CASE WHEN Number like '%@ourdomain.com' THEN 1 ELSE 2 END AS 'emailPriority'
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) as rn
FROM #emailOrPhone
WHERE Number like '%@%'
)

SELECT DISTINCT Names.Name, Emails.Number
FROM #emailOrPhone as Names
LEFT JOIN cte as Emails on Names.Name = Emails.Name and Emails.rn = 1
ORDER BY Names.Name, Emails.Number

DROP TABLE #emailOrPhone

Best Answer

Output

Related Solutions

Sql-server – Doing a comparison on two csv files with no Primary Key

SQL Server – How to Order by Custom Filter Excluding Certain Data

Related Question