SQL and unique n-coulmn combinations

Question

Is there an easy way in Oracle to request unique combinations of n fields. I have a very simple bipolar solution:

CREATE TABLE combinations AS SELECT 1 AS n FROM DUAL UNION ALL SELECT 2 FROM DUAL; 

Request for unique combinations:

 SELECT LEAST(xa, xb), GREATEST(xa,xb) FROM (SELECT c1.na, c2.nb FROM combinations c1 CROSS JOIN combinations c2 WHERE c1.n <> c2.n) x GROUP BY LEAST(xa, xb), GREATEST(xa,xb); 

Of this query, 1.2 and 2.1 are considered the same. Unfortunately, it does not work for a 3-field structure (for example, 1,2,3 should be considered the same as 3,1,2, because the order of the values ​​does not matter). Oracle analytic functions provide an appropriate solution to this issue? Could you suggest a specific analytic function from Oracle?

Answer 1

Your query for 2 columns can be rewritten as follows:

 SELECT c1.n, c2.n FROM combinations c1 INNER JOIN combinations c2 ON c1.n < c2.n 

For 3 columns, you will need to make some additions (in bold):

 SELECT c1.n, c2.n , c3.n FROM combinations c1 INNER JOIN combinations c2 ON c1.n < c2.n INNER JOIN combinations c3 ON c2.n < c3.n 

I am sure that now you can easily guess how to scale it for more columns.

Answer 2

Your solution is to use the pipeline function of the table to return your combinations.

Your function can live in its own package (with the state of the package), which returns the data you need in the order you want. A state is a set of variables determined by the number of returned columns, initialized with initial initial values ​​(A = 1, B = A + 1, C = B + 1, D = C + 1, etc.). Then you just

 PIPE ROW(a,b,c,d); -- increment D -- if D overflow limit, increment C and re-init D to C+1 -- if C overflow limit-1, increment B and re-init C to B+1 -- if B overflow limit-2, increment A and re-init B to A+1 -- if A overflow limit-3, the end 

This outputs for N = 6

  1,2,3,4 1,2,3,5 1,2,3,6 1,2,4,5 1,2,4,6 1,3,4,5 1,3,4,6 2,3,4,5 2,3,4,6 2,3,5,6 3,4,5,6