SQL Server permutations of 3 len (n) values with constraints
I am trying to create a permutation-only table of three values ("A", "B", "C") and a range (1, n) with specific constraints. Thinking about it from a python perspective, tuples, if there n=5
are (1A, 2A, 3A, 4B, 5C), (1A, 2A, 3, B, 4C, 5C), ...
Restrictions: Values ("A", "B", "C") must be present at least once in each set and must start with "A" and end with "C" (therefore 1 must = 'A' and 5 must = 'C'), but the middle may change, and only progress is alphabetical. So if:, n=5
then the result is a table like:
Col1 Col2 Col3
1 1 A
1 2 A
1 3 A
1 4 B
1 5 C
2 1 A
2 2 A
2 3 B
2 4 C
2 5 C
3 1 A
3 2 B
3 3 B
3 4 B
3 5 C
4 1 A
4 2 B
4 3 B
4 4 C
4 5 C
5 1 A
5 2 B
5 3 C
5 4 C
5 5 C
If Col1 refers to a set, Col2 is a numeric value from 1 to n, and Col3 is a variable A, B, or C
source to share
This is not an optimal problem for SQL. But you can do it with a recursive CTE:
with vals as (
select v.*
from (values ('A'), ('B'), ('C')) v(x)
),
cte as (
select cast('A' as varchar(max)) as str, 1 as len
union all
select (cte.str + vals.x), cte.len + 1
from cte cross join
vals
where cte.len < 5 - 1
)
select str + 'C'
from cte
where len = 5 - 1;
EDIT:
I have this strange suspicion that your use of "ordered permutation" means what you want. As follows Bs followed by Cs, but never another order. I don't think "ordered permutation" is the correct term, but a simple modification of the above query does the following:
with vals as (
select v.*
from (values ('A'), ('B'), ('C')) v(x)
),
cte as (
select cast('A' as varchar(max)) as str, 1 as len
union all
select (cte.str + vals.x), cte.len + 1
from cte join
vals
on vals.x >= right(cte.str, 1)
where cte.len < 5 - 1
)
select str + 'C'
from cte
where len = 5 - 1;
source to share