英文:
Sql query to regroup audit information
问题
| DataId | Time | Username | Column | Old value | New value |
|---|---|---|---|---|---|
| 1 | 2023-01-01 12:00:00 | User1 | DataC | Bazar2 | Bazar |
| 1 | 2023-01-01 06:00:00 | User1 | DataB | Much2 | Much |
英文:
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
答案1
得分: 3
以下是您要的翻译内容：
您可以将数据列解析为行，然后使用窗口函数来识别值的变化：
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](https://dbfiddle.uk/OJ6l-4IR)
<details>
<summary>英文:</summary>
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](https://dbfiddle.uk/OJ6l-4IR)
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论