你可以使用窗口功能 lead() 做分区 ObjectId . 这是演示。

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

输出：

| ObjectId  Value NewValue  UpdatedAt |

* -------------------------------------*

|  1        1       2      2020-07-16 |
|  1        2       3      2020-07-17 |
|  2        1       2      2020-07-16 |

您可以使用窗口函数来实现该结果。

DECLARE @historyTable table(ObjectId int, Value int, UpdatedAt date)

insert into @historyTable values
(1        ,     1 ,'2020-07-15'),
(1        ,     2 ,'2020-07-16'),
(1        ,     3 ,'2020-07-17'),
(2        ,     1 ,'2020-07-15'),
(2        ,     2 ,'2020-07-16');

SELECT * from
(
SELECT objectid, value as oldvalue,
lead(value,1) over (partition by objectid order by updatedat) as newvalue,
lead(UpdatedAt,1) over (partition by objectid order by updatedat) as updatedat
FROM @historyTable
) as t
where t.updatedat is not null

+----------+----------+----------+------------+
| objectid | oldvalue | newvalue | updatedat  |
+----------+----------+----------+------------+
|        1 |        1 |        2 | 2020-07-16 |
|        1 |        2 |        3 | 2020-07-17 |
|        2 |        1 |        2 | 2020-07-16 |
+----------+----------+----------+------------+

with cte as (
     select row_number() over (partition by objectid order by UpdatedAt ) rnk,[ObjectId], [Value], [UpdatedAt]
     from T
)
select t1.ObjectId,t2.Value as OldValue ,t1.Value as NewValue ,t1.UpdatedAt
from (
    select * from cte 
    where rnk <> 1
) t1
left join cte t2 on t1.objectid =t2.objectid and t1.rnk -1 = t2.rnk

表脚本：

CREATE TABLE T
    ([ObjectId] int, [Value] int, [UpdatedAt] datetime)
;

INSERT INTO T
    ([ObjectId], [Value], [UpdatedAt])
VALUES
    (1, 1, '2020-07-15 00:00:00'),
    (1, 2, '2020-07-16 00:00:00'),
    (1, 3, '2020-07-17 00:00:00'),
    (2, 1, '2020-07-15 00:00:00'),
    (2, 2, '2020-07-16 00:00:00')
;

演示链接

with changeLogTemp as
    (
    select ROW_NUMBER() over (order by ObjectId,UpdatedAt ) row_number ,* from changeLog
    )

    select 
    l.ObjectId,
    l.Value OldValue,
    r.Value NewValue,
    r.UpdatedAt 

    from changeLogTemp l 
    left join changeLogTemp r on l.ObjectId =r.ObjectId and l.row_number =r.row_number-1
    where r.UpdatedAt is not null

你也可以使用 Widowfunction (Lead()) 为了解决这个问题
但所提出的解决方案非常简单，同时对类似的操作也非常有用( computational and comparative )在行和两列或多列之间