SQL query for comparing two rows and getting the differences from same table

Question

I want to compare the two rows of same ID and I just want to get difference as result.

e.g.

NOW

|---ID---||--Col_1--||--Col_2--||--Col_3--||--Col_4--|
|----1---||----2----||----4----||----5----||----6----|
|----1---||----3----||----4----||----4----||----6----|
|----2---||----2----||----3----||----3----||----2----|

RESULT

|---ID---||--Col_1--||--Col_2--||--Col_3--||--Col_4--|
|----1---||----3----||---NULL--||----4----||---NULL--|

P.S : I'm using SQL Server 2012

Answer 1

If I am interpreting your question correctly, you want to combine the rows for the same id and apply the following rules:

• If the values are the same, then put the value in the row.
• If the values are different, then put in NULL.
• If there is only one row, then don't include the id.

This is an aggregation query with some filtering and comparison logic:

select id,
       (case when min(col1) = max(col1) then min(col1) end) as col1,
       . . .
from t
group by id
having count(*) > 1;