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 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
ode style="color:#006699;font-weight:bold;">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)