Sql query to regroup audit information


I have a SQL Server database where there is an history table containing a record each time the said record, in another table, changes.

Ie the source data table

Id DataA DataB DataC
1 Truc Much Bazar

Ie the history table

DataId Time Username DataA DataB DataC
1 2023-01-01 12:00:00 User1 Truc Much Bazar
1 2023-01-01 06:00:00 User1 Truc Much Bazar2
1 2023-01-01 00:00:00 User1 Truc Much2 Bazar2

Meaning the record 1 of the data table has been updated 3 times by user "User1", He changed "Much2" to "Much" and "Bazar2" to "Bazar". The last record (first line) beeing the current state.


I would like to make a query that shows the audit in a way we can see wich column has been updated, with the old value, the new one, and by who and when

In this example, it will bee like this :

DataId Time Username Column Old value New value
1 2023-01-01 12:00:00 User1 DataC Bazar2 Bazar
2 2023-01-01 06:00:00 User1 DataB Much2 Much

I’m clearly not an expert in SQL, and I don’t really know how to handle this. Any help will be appreciated

>Solution :

You can unpivot your data columns to rows, then use window functions to identify values that changed:

select dataid, time, username, col, lag_val as old_value, val as new_value
from (
    select t.*, v.*,
        lag(val, 1, val) over(partition by dataid, col order by time) lag_val
    from mytable t
    cross apply ( values ('DataA', DataA), ('DataB', DataB), ('DataC', DataC) ) v(col, val)
) t
where val <> lag_val
order by dataid, time
dataid time username col old_value new_value
1 2023-01-01 06:00:00.000 User1 DataB Much2 Much
1 2023-01-01 12:00:00.000 User1 DataC Bazar2 Bazar


Leave a ReplyCancel reply