CODEWITHSUNDEEP
sqlsql-server
Mike Payne
Mike Payne

Reputation: 107

Finding the difference between two nearly identical SQL rows

I am developing an application and my boss wants to track all changes that have been made to a record throughout its life.

For instance, if I have the following table:

ID Name   City             Item         Version
1  Mike   Miami            Test box     1
1  Mike   Fort Lauderdale  Test box     2
1  Mike   Sarasota         Testing box  3

And I want to see that from version 1 to version 2 the city was changed to Fort Lauderdale, is there a query that will help me do that? I would really like to be able to do this without specifying all the column names individually, because the actual table has 25+ columns and they may change at any time, plus it would be nice if the query could be easily portable to different tables. Ideally my result would look like the following, but I'm willing to accept anything that would help. Thanks in advance!

ColumnName    Previous Value    New Value
City          Miami             Fort Lauderdale

Upvotes: 0

Views: 218

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Assuming that the columns are all strings (which is rather necessary for your output format), you can do this by unpivoting the data and using lag():

select c.*
from (select t.id,
             lag(v.col) over (partition by t.id order by t.version) as prev_val,
             v.val
      from t cross apply
           (values ('Name', name), ('City', city), ('Item', item)
           ) v(col, val)
     ) c
where prev_val <> val;

If you have columns that are not strings, then you'll need to convert them to strings in the values clause.

This also assumes that the values are not NULL. That can be handled, but does not seem necessary.

Upvotes: 1

Related Questions