Как я могу отслеживать изменения нескольких столбцов в существующей таблице SQL Server, используя первичные ключи?

Question

В настоящее время я пытаюсь отслеживать изменения нескольких столбцов (назовем их col1 и col2) в таблице SQL Server. Таблица не обновляется / вставляется / удаляется с течением времени; новые записи только добавляются к нему (см. ниже 10/01 против 11/01).

Моей конечной целью было бы запустить SQL-запрос или хранимую процедуру, которая бы подсвечивала изменения с течением времени, используя первичные ключи, следуя структуре:

PrimaryKey | ColumnName | BeforeValue | AfterValue | Date

например

Оригинальный стол:

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

Выход:

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

Пожалуйста, дайте мне знать, если у вас есть какие-либо советы, ссылки или вам нужна дополнительная информация, это было бы очень полезно!

огромное спасибо

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