sql-server - Track the changes of a few columns in an existing table leveraging primary keys?

Question

I'm currently trying to track the changes of a few columns (let's call them col1 & col2) in a SQL Server table. The table is not being "updated/inserted/deleted" over time; new records are just being added to it (please see below 10/01 vs 11/01).

My end-goal would be to run a SQL query or stored procedure that would highlight the changes overtime using primary keys following the framework:

PrimaryKey | ColumnName | BeforeValue | AfterValue | Date

e.g.

Original table:

+-------+--------+--------+--------+
|  PK1  |  Col1  |  Col2  |  Date  |
+-------+--------+--------+--------+
|   1   |    a   |    e   |  10/01 |  
|   1   |    b   |    e   |  11/01 |
|   2   |    c   |    e   |  10/01 |
|   2   |    d   |    f   |  11/01 |
+-------+--------+--------+--------+

Output:

+--------------+--------------+---------------+--------------+--------+
|  PrimaryKey  |  ColumnName  |  BeforeValue  |  AfterValue  |  Date  |
+--------------+--------------+---------------+--------------+--------+
|      1       |     Col1     |       a       |       b      |  11/01 |
|      2       |     Col1     |       c       |       d      |  11/01 |
|      2       |     Col2     |       e       |       f      |  11/01 |
+--------------+--------------+---------------+--------------+--------+  

Any help appreciated.

标签： sql-servertsqlaudit-loggingaudit-tables

Answer 1

这是一些有点笨拙但似乎有效的代码，基本上对于每一行，我都尝试找到具有不同值的较早行。这会进行两次，一次用于 Col1，一次用于 Col2。

为了使它工作，我必须添加一个唯一的 PK 字段，我不知道您是否有，您可以轻松地将其作为识别字段添加到您的真实表或用于计算的表中。

declare @TestTable table (PK int, PK1 int, Col1 varchar(1), Col2 varchar(1), [Date] date)

insert into @TestTable (PK, PK1, Col1, Col2, [Date])
  select 1, 1, 'a', 'e', '10 Jan 2018'
  union all select 2, 1, 'b', 'e', '11 Jan 2018'
  union all select 3, 2, 'c', 'e', '10 Jan 2018'
  union all select 4, 2, 'd', 'f', '11 Jan 2018'

  select T1.[Date], T1.PK1, 'Col1', T2.Col1, T1.Col1
  from @TestTable T1
  inner join @TestTable T2 on T2.PK = (
      select top 1 PK
      from @TestTable T21
      where T21.PK1 = T1.PK1 and T21.Col1 != T1.Col1 and T21.[Date] < T1.[Date]
      order by T21.[Date] desc
    )

  union all

  select T1.[Date], T1.PK1, 'Col2', T3.Col2, T1.Col2
  from @TestTable T1
  inner join @TestTable T3 on T3.PK = (
      select top 1 PK
      from @TestTable T31
      where T31.PK1 = T1.PK1 and T31.Col2 != T1.Col2 and T31.[Date] < T1.[Date]
      order by T31.[Date] desc
    )

  order by [Date], PK1