乾学院 1750 号会员
1 回帖  •  403 浏览  • 
     ...

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

 
sql(14) json(16) 转置(1) 行转列(3) 列转行(2) 过滤(5)

问题

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|


JSONOUTPUTExpected

[
{
"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

解答

将数据行转列，然后过滤出2、3两列不相同的那些记录，最后转成json即可。这类运算用SQL写起来很麻烦，需要自关联后使用CROSS APPLY (VALUES … 转置后再过滤，写出来的SQL冗长。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要两句：

A

1

=MSSQL.query("SELECT * FROM t")

2

=json(A1.pivot@r(id;ColumnName,value).pivot(ColumnName;id,value;"id1":"Value1","id2":"Value2").select(#2!=#3))

SPL源代码：https://github.com/SPLWare/esProc

问答搜集

sql(14) json(16) 转置(1) 行转列(3) 列转行(2) 过滤(5)