How to comapre two rows in SQL Server

Question

Consider the below rows in a table

ID  | Fname  | Lname   | Age | Weight 
-----------------------------------
23  | Kareem | Benzema | 30  | 75
24  | Karim  | Benzema | 32  | 75

I want to compare two rows and get difference between them. My required result would be:

ColumnName | OldValue | NewValue
---------------------------------
ID         | 23       | 24
---------------------------------
Fname      | Kareem   | Karim
---------------------------------
Age        | 30       | 32 

Answer 1

...adjust..

declare @t table(ID int, Fname varchar(50), Lname varchar(50), Age tinyint, Weight smallint);

insert into @t(ID, Fname, Lname, Age, Weight)
values
(23, 'Kareem', 'Benzema', 30, null),
(24, 'Karim', 'Benzema', 32, 75);


select r1.[key], r1.value as oldvalue, r2.value as newvalue
from
(
    select 
        max(case when dt.rownum=1 then dt.thejson end) as row1,
        max(case when dt.rownum=2 then dt.thejson end) as row2
    from
    (   
        select 
            row_number() over(order by /*ID ?*/ @@spid) as rownum,
            (select t.* for json path, include_null_values, without_array_wrapper) thejson
        from @t as t
        where ID in (23, 24)--input
    ) as dt
) as src
cross apply openjson(src.row1) as r1
cross apply openjson(src.row2) as r2
where r1.[key] = r2.[key]
and (r1.value <> r2.value or r1.type <> r2.type) --type for null?
;