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.

Answer 1

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