Calculate all unique combinations of one list (cartesian product)

Von Tobias Arnhold 1.15.2015
A couple of days ago a had a task to combine a list with itself. The result should be a two column report of each possible combination.

What should not be part of the result:
- same element combinations should be excluded
- duplicate rows (even if the sides are switched)

Because I couldn't solve it myself *actually it's awful after I now know how to do it* I had to create a forum entry:
Oracle SQL and PL/SQL forum: Generate some kind of cartesian list
Time to try solving it myself (thinking/searching the www): 40 minutes --> Result: fail
Forum reaction time: 12 minutes --> Result: win (Thanks to Marwim and Karthick_Arp)

That's the way how it works:
-- Table name: MY_TAB
-- Columns: ID (VARCHAR2(100))
-- Insert
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.197');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.198');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.199');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.202');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.203');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.204');  
 INSERT INTO MY_TAB ( ID ) VALUES ('10.251.14.205');  
And here is the select statement:
 SELECT  
  S1.ID as A,  
  S2.ID as B  
  FROM MY_TAB S1  
  CROSS JOIN MY_TAB S2  
  WHERE S1.ID < S2.ID
Mostly important is the "<" it kicks all unnecessary combinations.

Post Tags:

No Comment to " Calculate all unique combinations of one list (cartesian product) "