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

CodePudding user response：

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

CodePudding user response：

Answer:

A possible solution is a CROSS JOIN with an additional APPLY operator:

The test data from the fiddle:

CREATE TABLE companies ( 
   Id int,
   company_name  VARCHAR(40),
   address_type  VARCHAR(40),
   address    VARCHAR(40)
);
INSERT INTO companies VALUES (1,'Company A','Billing','111 Street');
INSERT INTO companies VALUES (2,'Company A','Shipping','112 Street');

Stored procedure:

CREATE PROCEDURE uspColumnsAsJson 
   @Id1 int,
   @Id2 int
AS
BEGIN
   SELECT a.ColumnName, a.Value1, a.Value2
   FROM (
      SELECT 
         c1.company_name AS company_name1, 
         c1.address_type AS address_type1, 
         c1.address AS address1, 
         c2.company_name AS company_name2, 
         c2.address_type AS address_type2, 
         c2.address AS address2 
      FROM companies c1
      CROSS JOIN companies c2
      WHERE c1.Id = @Id1 AND c2.Id = @Id2
   ) t
   CROSS APPLY (VALUES
      ('company_name', CONVERT(varchar(max), t.company_name1), CONVERT(varchar(max), t.company_name2)),
      ('address_type', CONVERT(varchar(max), t.address_type1), CONVERT(varchar(max), t.address_type2)),
      ('address', CONVERT(varchar(max), t.address1), CONVERT(varchar(max), t.address2))
   ) a (ColumnName, Value1, Value2)
   WHERE a.Value1 <> a.Value2
   FOR JSON AUTO
END

EXEC uspColumnsAsJson 1, 2

Result:

[
{"ColumnName":"address_type","Value1":"Billing","Value2":"Shipping"},
{"ColumnName":"address","Value1":"111 Street","Value2":"112 Street"}
]

Update:

If you want to include all columns, you need a dynamic statement based on the system catalog views:

CREATE PROCEDURE uspColumnsAsJson 
   @Id1 int,
   @Id2 int
AS
BEGIN
   DECLARE @stmt nvarchar(max)
   DECLARE @prms nvarchar(max)
   DECLARE @err int

   -- APPLY part
   SELECT @stmt = STRING_AGG(
      CONCAT(
         N'(''',
         col.[name],
         N''', CONVERT(varchar(max), c1.',
         QUOTENAME(col.[name]),
         N'), CONVERT(varchar(max), c2.',
         QUOTENAME(col.[name]),
         N'))'
      ),
      N','
   )   
   FROM sys.columns col
   JOIN sys.tables tab ON col.object_id = tab.object_id
   JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
   WHERE (tab.[name] = 'companies') AND (sch.[name] = 'dbo') AND (col.[name] <> 'Id')
   
   -- Whole statement
   SET @stmt = CONCAT(
      N'SELECT a.ColumnName, a.Value1, a.Value2 ',
      N'FROM companies c1 ',
      N'CROSS JOIN companies c2 ',
      N'CROSS APPLY (VALUES ',
      @stmt, 
      N') a (ColumnName, Value1, Value2) ',
      N'WHERE (c1.Id = @Id1) AND (c2.Id = @Id2) AND (a.Value1 <> a.Value2) ',
      N'FOR JSON AUTO '
   )
   
   -- Execution
   SET @prms = N'@Id1 int, @Id2 int'
   EXEC @err = sp_executesql @stmt, @prms, @Id1, @Id2
   RETURN @err
END