How to Compare 2 Rows from the Same Table (SQL Server)

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

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

How to compare two rows from same table while sorting in SQL?

Specify both columns in ORDER BY. If the values in first column have a tie, the second column is considered (and so on):

ORDER BY total_score DESC, mcq_score DESC

Compare two rows (both with different ID) & check if their column values are exactly the same. All rows & columns are in the same table

Please try the following solution based on the ideas of John Cappelletti. All credit goes to him.

SQL

-- DDL and sample data population, start
DECLARE @roster TABLE (ID INT PRIMARY KEY, NAME VARCHAR(10), TIME CHAR(4));
INSERT INTO @roster (ID, NAME, TIME) VALUES
(1,'N1','0900'),
(2,'N1','0801')
-- DDL and sample data population, end

DECLARE @source INT = 1
    , @target INT = 2;

SELECT id AS source_id, @target AS target_id
      ,[key] AS [column]
      ,source_Value = MAX( CASE WHEN Src=1 THEN Value END)
      ,target_Value = MAX( CASE WHEN Src=2 THEN Value END)
FROM (
        SELECT Src=1
              ,id 
              ,B.*
         FROM @roster AS A
         CROSS APPLY ( SELECT [Key]
                             ,Value
                       FROM OpenJson( (SELECT A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES)) 
                     ) AS B
        WHERE id=@source
        UNION ALL
        SELECT Src=2
              ,id = @source
              ,B.*
         FROM @roster AS A
         CROSS APPLY ( SELECT [Key]
                             ,Value
                       FROM OpenJson( (SELECT A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES)) 
                     ) AS B
         WHERE id=@target
      ) AS A
GROUP BY id, [key]
HAVING MAX(CASE WHEN Src=1 THEN Value END)
     <> MAX(CASE WHEN Src=2 THEN Value END)
    AND [key] <> 'ID'   -- exclude this PK column
ORDER BY id, [key];

Output

+-----------+-----------+--------+--------------+--------------+
| source_id | target_id | column | source_Value | target_Value |
+-----------+-----------+--------+--------------+--------------+
|         1 |         2 | TIME   |         0900 |         0801 |
+-----------+-----------+--------+--------------+--------------+

---

Related Topics