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:
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.IDMostly important is the "<" it kicks all unnecessary combinations.
