Finding Permutations of columns in SQL

Monday, May 12, 2014

I have a reference data table having columns as codes and values.

For e.g. there are three code types viz. A, B, C.

The table is as below:

Code Value
A1 a_one
A2 a_two
B1 b_one
B2 b_two
B3 b_three
C1 c_one
C2 c_two
C3 c_three
C4 c_four

I have a requirement where the input will be code types and output should be all permutations between the input code types.

For e.g. if the input code types are A and C, the output of my sql should be:

col_1 col_2
A1 C1
A1 C2
A1 C3
A1 C4
A2 C1
A2 C2
A2 C3
A2 C4

Similarly if the input code types is A, B, C, the output of the sql will have three columns with all the permutations between A, B, C viz. A1 B1 C1 to A2 B3 C4.

I have no idea how to start on this. So any hints will be useful.

Thanks for reading!