【Question】

I want to compare two rows of data in a table to get their different columns. The original data has the following structure:
ID  v1  v2  v3  v4…
1  a  b  c  d
2  a  c  c  d

Expected result:
ID  v2
1  b
2  c

In the source table, each ID corresponds to hundreds of parameters. I need to find if there are parameters that have changed in a pair of rows having same ID and get column values the change begins.

【Answer】

Your processing is based on two rows of data instead of the whole table. It’s very difficult to handle discrete rows of data in SQL since the language is batch-process-oriented. It’s also hard to return a dynamic result set in SQL. So you need to retrieve the rows out of the database and compare them using, for example, the stored procedure. But expressing a set operation and achieving dynamic columns with the stored procedure are not simple. You can make the whole thing much simpler with SPL. The Structured Process Language supports processing data in an intuitive way. In your case, the natural way is to find the column containing different values and create a result table sequence in the specified format to hold the desired values.

A1: Connect to test database.

A2: Query the source table for a pair of rows through parameter id_, which passes id values in dynamically.

A3, A4: Get two records having same id and convert their field values into arrays.

A5: Compare values at the same position in the two arrays and if they are different, get their index number.

A6: Get the column names from the source table according to A5’s index numbers and return them as a string.

A7: Create the result set according to A2’s table sequence.

Below is part of the source table:

A2

A3, A4

A5, A6

A7