显示同一表中行之间的差异

Question

我有一个名为Employee_audit的表，其模式如下，

emp_audit_id…1000…1000…3000…

Answer 1

这里有一个选项，可以动态地解除数据的枢轴，而无需实际使用动态SQL。

;with cte as (
Select emp_audit_id
,eid
,[key]
,newValue=[value]
,oldvalue = lag(value) over (partition by eid,[key] order by emp_audit_id)
From Employee_audit A
Cross Apply ( Select [key],[value] From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper  )  ) ) B
Where [key] not in ('emp_audit_id','eid')
)
Select emp_audit_id
,columName = [key]
,oldvalue
,newvalue 
From  cte
Where newvalue<>oldvalue or oldvalue is null

emp_audit_id    columName   oldvalue    newvalue
1               department  NULL        ROP
1               name        NULL        Daniel
2               name        Daniel      Dani
3               name        Dani        Danny
1               salary      NULL        1000
3               salary      1000        3000

Answer 2

我将使用apply:

select t.emp_audit_id, v.columnName, v.newValue,
lag(v.newValue) over (partition by eid, columnName order by emp_audit_id) as oldValue
from some_table t cross apply
(values ('name', t.name),
('salary', t.salary), 
. . .
) v (columnName, newValue);

如果需要将值转换为字符串，则在values子句中:

select t.emp_audit_id, v.columnName, v.newValue,
lag(v.newValue) over (partition by eid, ColumnName order by emp_audit_id) as oldValue
from some_table t cross apply
(values ('name', t.name),
('salary', cast(t.salary as varchar(255))), 
. . .
) v (columnName, newValue);