How to compare two row in same table and return the data in response using stored procedure

I have to compare between two rows using two Id’s in the same table, and I want to get the columns and their values that are not matching in the stored procedure and I need to return it in JSON format.

     |Col1|Col2|Col3|Col4|
Id-1 |ABC |123 |321 |111 |
Id-2 |ABC |333 |321 |123|

Output:

     |col2|col4|
Id-1 |123 |111 |
Id-2 |333 |123 |


JSON OUTPUT Expected

[
   {
      "ColumnName":"COL2",
      "Value1":"123",
      "Value2":"333"
   },
   {
      "ColumnName":"COL4",
      "Value1":"111",
      "Value2":"123"
   }
]

I don’t have expertise in it however I tried the below SQL code but I need this in a very nice way and that too in a stored procedure and it should be returned in JSON format, please help!

What I have tried, please check the link below with sample example and query.

SQL Fiddle

Answer

You need to unpivot all the columns, then join each row to every other.

You can either pivot everything manually using CROSS APPLY (VALUES

SELECT
  aId = a.id,
  bId = b.id,
  v.columnName,
  v.value1,
  v.value2
FROM @t a
JOIN @t b
    ON a.id < b.id
 -- alternatively
 -- ON a.id = 1 AND b.id = 2
CROSS APPLY (VALUES
   ('col1', CAST(a.col1 AS nvarchar(100)), CAST(b.col1 AS nvarchar(100))),
   ('col2', CAST(a.col2 AS nvarchar(100)), CAST(b.col2 AS nvarchar(100))),
   ('col3', CAST(a.col3 AS nvarchar(100)), CAST(b.col3 AS nvarchar(100))),
   ('col4', CAST(a.col4 AS nvarchar(100)), CAST(b.col4 AS nvarchar(100)))
) v (ColumnName, Value1, Value2)
WHERE EXISTS (SELECT v.Value1 EXCEPT SELECT v.Value2)
FOR JSON PATH;

The use of WHERE EXISTS (SELECT a.Value1 INTERSECT SELECT a.Value2) means that nulls will get taken into account properly.

---

Or you can use SELECT t.* FOR JSON and unpivot using OPENJSON

WITH allValues AS (
    SELECT
      t.id,
      j2.[key],
      j2.value,
      j2.type
    FROM @t t
    CROSS APPLY (
        SELECT t.*
        FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
    ) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    WHERE j2.[key] <> 'id'
)
SELECT
  aId = a.id,
  bId = b.id,
  columnName = a.[key],
  value1 = a.value,
  value2 = b.value
FROM allValues a
JOIN allValues b ON a.[key] = b.[key]
    AND a.id < b.id
 -- alternatively
 -- AND a.id = 1 AND b.id = 2
WHERE a.type <> b.type
   OR a.value <> b.value
FOR JSON PATH;

db<>fiddle

SQL Fiddle of actual data