Permutations of a Table Column in SQL

The permutation of a set comprises all the unique orderings of the elements of the set. Naive implementation of a permutation generator in SQL requires performing a N-ary self-join of the set, where N is the number of elements in the set. This is inconvenient, as the code must be reworked whenever the set size changes. By taking advantage of features in SQL Server 2005 and later, the implementation below generates the permutations of a column in an input table using completely static SQL.The output is in lexicographic (ie dictionary) order with each permutation identified by it's lexicographic sequence number.

The variable @delim can be set to the desired delimiter character in the output permutations, and defaults to comma (',') in the code below. Proper inclusion of one element within another, as 'Ann' in 'JoAnne', is properly handled.

select * 
into #data
from (
    values
        ('Ann'),
        ('John'),
        ('Mark'),
        ('Fred'),
        ('JoAnne'),
        ('George')
) T(name)
;
declare @num    as int;
declare @delim  as char(1);
set @num    = (select count(*) from #data);
set @delim  = ',';
;
-- Permutations of #t
with T (name, level) as  (
   select convert(varchar(max), @delim+name+@delim), level=1 from  #data
   union all
   select convert(varchar(max),T.name+#data.name+@delim),level+1 
   from #data,T
   where level  < @num
     and charindex(@delim+#data.name+@delim,T.name) = 0
)
select
   RowNo = Row_Number() over (order by  name),
   substring(name,2,len(name)-2) as  permutation
from T
where level  = @num
order by  name
 
drop table  #data
;

---

RowNo name

---

1 Ann,Fred,John,Mark
2 Ann,Fred,Mark,John
3 Ann,John,Fred,Mark
4 Ann,John,Mark,Fred
5 Ann,Mark,Fred,John
6 Ann,Mark,John,Fred
7 Fred,Ann,John,Mark
8 Fred,Ann,Mark,John
9 Fred,John,Ann,Mark
10 Fred,John,Mark,Ann
11 Fred,Mark,Ann,John
12 Fred,Mark,John,Ann
13 John,Ann,Fred,Mark
14 John,Ann,Mark,Fred
15 John,Fred,Ann,Mark
16 John,Fred,Mark,Ann
17 John,Mark,Ann,Fred
18 John,Mark,Fred,Ann
19 Mark,Ann,Fred,John
20 Mark,Ann,John,Fred
21 Mark,Fred,Ann,John
22 Mark,Fred,John,Ann
23 Mark,John,Ann,Fred
24 Mark,John,Fred,Ann

(24 row(s) affected)