Calculating permutations with SQL

The SQL lines below implement the output you want. Just a little coding in SQL environment

Declare @resultTable Table
(
  role nvarchar(100),
  name nvarchar(100)
)


-- Keep track of @temp1 record processing
Declare @role varchar(100)
Declare @name varchar(100)
Declare @loopCounter INT = 1

While((Select Count(*) From roleTable)>= @loopCounter)
Begin

  DECLARE @counter INT = 0;
  

  Set @role = (SELECT Top 1 role FROM (SELECT ROW_NUMBER() OVER(ORDER BY role desc) AS RoNum , role FROM roleTable) AS tbl WHERE @loopCounter = RoNum)

  Set @name = (SELECT Top 1 name FROM (SELECT ROW_NUMBER() OVER(ORDER BY role desc) AS RoNum , name FROM roleTable) AS tbl WHERE @loopCounter = RoNum)

  DECLARE @recordCount INT = (Select Count(*) From roleTable where role = @role)


  if(@recordCount = 1)
  begin
    
    while(@counter < 4)
      begin
        insert into @resultTable values(@role,@name)
         SET @counter = @counter + 1;
      end
  end
  else if(@recordCount = 2)
  begin
    while(@counter < 2)
      begin
        insert into @resultTable values(@role,@name)
         SET @counter = @counter + 1;
      end
  end
  else if(@recordCount = 3)
  begin
    while(@counter < 1)
      begin
        insert into @resultTable values(@role,@name)
         SET @counter = @counter + 1;
      end
  end
  else if(@recordCount = 4)
  begin
    insert into @resultTable values(@role,@name)
  end

  

  set @loopCounter = @loopCounter + 1

 
End

;WITH newTable AS (
SELECT 
t.role,
t.name, 
ROW_NUMBER() OVER (
                   PARTITION BY t.role 
                   ORDER BY role
                   ) AS [lineup]
FROM @resultTable t)
select * from newTable
order by role desc

output:

You can use a recursive CTE to build the combinations. First, you need to compute the number of times any role should exist in the full output. This is accomplished in the prod and p CTEs. Then, to build the actual combination output list, you need to take the value from the p CTE and offset each unique role count from the records in the original input table (named lineup in my code below) by the value from p:

with recursive s as (select row_number() over (order by (select 1)) r, role, count(*) c from lineup group by role),
     l_mod as (select row_number() over (order by (select 1)) r, l.* from lineup l),
     prod(c, v) as (
        select 1, (select s1.c from s s1 where s1.r = 1)
        union all
        select p.c+1, p.v*(select s1.c from s s1 where s1.r = p.c+1) from prod p where p.c < (select count(*) from s)
     ),
     p as (select v from prod order by c desc limit 1),
     new_l(r, role, name, b_c, l_c) as (
        select l.*, 1, (select * from p)/(select s1.c from s s1 where s1.role = l.role) from l_mod l
        union all
        select nl.r, nl.role, nl.name, nl.b_c + 1, nl.l_c from new_l nl where nl.b_c < nl.l_c 
     ),
     final_l as (select row_number() over (order by r) r1, role, name from new_l order by r)
select l.role, l.name, (select sum(l1.r1 < l.r1 and l1.role = l.role) from final_l l1)+1 from final_l l;

Ajax1234 is correct in "You can use a recursive CTE to build the combinations". Stored procedure might also work but are not available in SQLite. Neither answer gave me all the unique combinations (maybe I didn't explain my question well). Anyway this is what I ended up using and seems to work:

with recursive

-- index the players in each role
players as
(
    select
        row_number() over (partition by role order by name) player_num,
        role,
        name
    from t1
),

-- index the roles
roles as
(
    select row_number() over (order by role) role_num, role, count(*) player_cnt, ln(count(*)) ln_player_cnt
    from players
    group by role
),

-- add role_num to players
players2 AS
(
    select a.*, role_num
    from players a
    join roles b on a.role=b.role
),

-- calculate running permutation count
--  ex. role#1 has 2 members, running_perm_cnt=2   (2)
--      role#2 has 1 member,  running_perm_cnt=2   (2 x 1)
--      role#3 has 5 members, running_perm_cnt=10  (2 x 1 x 5)
--      role#4 has 3 members, running_perm_cnt=30  (2 x 1 x 5 x 3)
roles2 as
(
    select *,
        round(exp(sum(ln_player_cnt) OVER (ROWS UNBOUNDED PRECEDING)),1) running_perm_cnt
    from roles
),

-- calculate roles count, permutations count and an
ttl as (
    select count(*) role_cnt, max(running_perm_cnt) perm_cnt,  max(running_perm_cnt) * count(*) ttl_cnt
    from roles2
),

-- use recursive CTE to build the permutations
perm(n,role_num,player_num,lineup) AS (
SELECT
    1,
    1,
    1,
    0
UNION ALL
SELECT
    n + 1,
    floor(n/perm_cnt)+1,
    (floor(n/(perm_cnt/running_perm_cnt)) % player_cnt) + 1,
    n % perm_cnt
FROM
    perm, ttl, roles2 c
WHERE n < ttl_cnt AND c.role_num = floor(n/perm_cnt) + 1
),

-- join permutations back to players table so we have names
select a.*, b.role, b.name
from perm a
    left outer join players2 b on a.role_num=b.role_num and a.player_num=b.player_num