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.

@MitchWheat, I have never used Change Tracking or CDC but after looking at the documentation [link](https://www.red-gate.com/simple-talk/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/) it looks like it doesn't offer the primary key feature I was trying to describe above (please take a look at the output table). Thank you so much! — Jeremie, Oct 23 '18 at 17:08

score 1 · Accepted Answer · answered Oct 23 '18 at 00:24

Here is some code which is a bit clunky, but seems to work, Basically for each row I try and find an earlier row with a different value. This is done twice, once for Col1 and once for Col2.

To make it work I had to add a unique PK field, which I don't know whether you have or not, you can easily add as an identify field, either to your real table, or to the table used for the calculations.

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

Thank you so much. It helped a lot. I added a two "group by": T1.PK1, T2.Col1, T1.Col1 & T1.PK1, T3.Col2, T1.Col2 for the two select statements to handle "select 5, 2, 'z', 'f', '12 Jan 2018'" — Jeremie, Oct 23 '18 at 20:45

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

1 Answers1