Answer

Source:https://stackoverflow.com/questions/70531046/check-if-values-are-the-same-for-each-row-over-criteria-in-different-other-colum

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

To do your task, we transform data rows to column, select rows where column 2 and column 3 are different, and then convert the result set to JSON format. It is roundabout and complicated to try to handle such a scenario in SQL because you need to perform a self-join, use CROSS APPLY (VALUES … to perform the transposition, and then filter data. The code is extremely long. The usual way is to fetch the original data out of the database and process it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate simple and concise code. It accomplishes the task with only two lines of code:

View SPL source code.