Build change history from table with historical row values

I have table that is storing historical values for data in other table:

ObjectId | Value | UpdatedAt
1        |     A | 2020-07-15
1        |     B | 2020-07-16
1        |     C | 2020-07-17
2        |     A | 2020-07-15
2        |     B | 2020-07-16

Now I need to generate from such table “change log” which will show what was old value, new value and when update occur:

ObjectId | OldValue | NewValue | UpdatedAt
1        |     A    |        B | 2020-07-16
1        |     B    |        C | 2020-07-17
2        |     A    |        B | 2020-07-16

Unfortunately, I can’t change structure of the existing table and can’t put old value there, I need a query to extract this.

Answer

You can use window function lead() to do partition on ObjectId. Here is the demo.

select
  ObjectId,
  Value,
  NewValue,
  UpdatedAt
from
(
  select
    ObjectId,
    Value,
    lead(value) over (partition by ObjectId order by UpdatedAt) as NewValue,
    lead(UpdatedAt) over (partition by ObjectId order by UpdatedAt) as UpdatedAt
  from Table1
) subq
where NewValue is not null
order by ObjectId

output:

| ObjectId  Value NewValue  UpdatedAt |
*-------------------------------------*
|  1        1       2      2020-07-16 |
|  1        2       3      2020-07-17 |
|  2        1       2      2020-07-16 |