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!







http://ift.tt/1quU6qv