topic Generate pairwise combinations in HANA SQL in Application Development and Automation Discussions

Generate pairwise combinations in HANA SQL

martin_chambers — Tue, 14 Mar 2023 15:32:08 GMT

Currently, I have a list of records that looks a bit like this (it is actually much longer)

I would like to generate all pairwise combinations of Field_B with the same value in Field_A.
If there is only one vleue in Field_B assigned to Field_A, no combination can be generated. Also, a combination of 2 identical vlaues in Field C and Field D in 1 record should not be generated.
The combinations would be stored in Field_C and Field D

The result would look like this

How can I create this result using HANA SQL?

Re: Generate pairwise combinations in HANA SQL

Yogananda — Tue, 14 Mar 2023 18:20:54 GMT

martin.chambers
The following query can be used to generate the desired result in SAP HANA:

SELECT
  Field_A,
  Field_B,
  CASE
    WHEN Field_A = Lag(Field_A) OVER (ORDER BY Field_A) THEN Lag(Field_B) OVER (ORDER BY Field_A)
    ELSE NULL
  END AS Field_C,
  Field_B AS Field_D
FROM mytable
ORDER BY Field_A;

If this response answers your query, please accept it and close the thread.
Have a wonderful day!

Re: Generate pairwise combinations in HANA SQL

martin_chambers — Wed, 15 Mar 2023 15:09:03 GMT

Hi Muthaiah,

thank you very much for your quick response. I tried your SQL query with my data.

The SQL query results for Field A = 2 are not what I had hoped for. I has been expecting.
a) The fields C and D are swapped. This is not important.
b) a "C" in Field C in the 2nd row.
c) Another 3 records like the ones with Field A = 2 except that the values for Field C and Field D are swapped.

Do you have an idea how to change this?

Regards
Martin

Re: Generate pairwise combinations in HANA SQL

martin_chambers — Sun, 26 Mar 2023 11:49:34 GMT

I have found a solution.

I just create a self join using only Field A in the on condition. This will create a cartesian product of all records with the same value in Field A. In other words, I have created the paired combinations.