If you google “generating permutations using SQL”, you get thousands of hits. It’s an interesting problem if not very useful.
I wrote a solution recently and thought I’d share it. If you’re keen, try tackling it yourself before moving on.
Notice the use of recursive CTEs as well as bitmasks and the exclusive or operator (^).
with Letters as ( select letter from ( values ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), ('i') ) l(letter) ), Bitmasks as ( select cast(letter as varchar(max)) as letter, cast(power(2, row_number() over (order by letter) - 1) as int) as bitmask from Letters ), Permutations as ( select letter as permutation, bitmask from Bitmasks union all select p.permutation + b.letter, p.bitmask ^ b.bitmask from Permutations p join Bitmasks b on p.bitmask ^ b.bitmask > p.bitmask ) select permutation from Permutations where bitmask = power(2, (select count(*) from Letters)) - 1
362880 rows (9!) in less than ten seconds. Let me know what you come up with.