Niko Gamulin - 9 months ago 43

SQL 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

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.

Source (Stackoverflow)