How to generate a permutations or combinations of n rows in m columns?

Question

Does anyone know how to create a table with m columns from table of n rows where the values in columns of each row represent a different combination or permutation of values from the original table?

For example the original table has 1 column (number_value) with 3 (n=3) rows:

1 2 3 

The table which contains combinations (the order doesn't matter) of two values (m = 2) would be the following:

number1, number2 1,2 1,3 2,3 

and the table of permutations would be the following:

number1, number2 1, 2 2, 1 1, 3 3, 1 2, 3 3, 2 

The order of rows doesn't matter.

Thank you in advance!

Answer 1

Combinations:

SELECT T1.x, T2.x FROM your_table T1 JOIN your_table T2 ON T1.x < T2.x 

Permutations:

SELECT T1.x, T2.x FROM your_table T1 JOIN your_table T2 ON T1.x != T2.x 

I am assuming that the values in the original table are unique.

To generalize for larger values of m you need to add more joins.