SQL Server Sql query to regroup audit information

1l5u6lss 于 2023-04-04 发布在其他

关注(0)|答案(1)|浏览(81)

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

The source data table:
| Id | DataA | DataB | DataC |
| ------------ | ------------ | ------------ | ------------ |
| 1 | Truc | Much | Bazar |

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) being the current state.

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

In this example, it will be 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

sql-server

来源：https://stackoverflow.com/questions/75900315/sql-query-to-regroup-audit-information

1条答案

按热度按时间

pokxtpni1#

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

fiddle

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Sql query to regroup audit information

1条答案

相关问题

热门标签

最新问答