https://www.youtube.com/watch?v=HiscSRv7zWk

drop table if exists job_positions;
create table job_positions
(
	id			int,
	title 		varchar(100),
	groups 		varchar(10),
	levels		varchar(10),
	payscale	int,
	totalpost	int
);
insert into job_positions values (1, 'General manager', 'A', 'l-15', 10000, 1);
insert into job_positions values (2, 'Manager', 'B', 'l-14', 9000, 5);
insert into job_positions values (3, 'Asst. Manager', 'C', 'l-13', 8000, 10);

drop table if exists job_employees;
create table job_employees
(
	id				int,
	name 			varchar(100),
	position_id 	int
);
insert into job_employees values (1, 'John Smith', 1);
insert into job_employees values (2, 'Jane Doe', 2);
insert into job_employees values (3, 'Michael Brown', 2);
insert into job_employees values (4, 'Emily Johnson', 2);
insert into job_employees values (5, 'William Lee', 3);
insert into job_employees values (6, 'Jessica Clark', 3);
insert into job_employees values (7, 'Christopher Harris', 3);
insert into job_employees values (8, 'Olivia Wilson', 3);
insert into job_employees values (9, 'Daniel Martinez', 3);
insert into job_employees values (10, 'Sophia Miller', 3);

select * from job_positions;
select * from job_employees;

WITH RECURSIVE
ROW_NUMBER()

WITH RECURSIVE CTE AS (
	SELECT id AS pos_id,title,grou,levels,payscale,totalpost,1 as counter FROM job_positions
    UNION ALL
	SELECT pos_id,title,grou,levels,payscale,totalpost,counter + 1
    FROM CTE
    WHERE counter < totalpost
),cte2 AS (
select * 
	,row_number() OVER (partition by position_id) AS rn
from job_employees
)
SELECT title,grou,levels,payscale,coalesce(cte2.name,'Vacant') as emp_name FROM cte left join cte2 ON cte.counter = cte2.rn AND cte.pos_id = cte2.position_id
order by pos_id