Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Generate pairwise combinations in HANA SQL

martin_chambers
Participant
0 Kudos
373

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?

  • SAP Managed Tags:
1 ACCEPTED SOLUTION

martin_chambers
Participant
0 Kudos
192

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.

  • SAP Managed Tags:
3 REPLIES 3

yogananda
Product and Topic Expert
Product and Topic Expert
0 Kudos
192

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!


  • SAP Managed Tags:

0 Kudos
192

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

  • SAP Managed Tags:

martin_chambers
Participant
0 Kudos
193

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.

  • SAP Managed Tags: