Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
0


create table mytable (region, role, employees) as
select 'Africa', 'Analyst', 2 from dual union all
select 'Africa', 'S. Analyst', 3 from dual union all
select 'Africa', 'Manager', 9 from dual union all
select 'India', 'Partner', 2 from dual union all
select 'India', 'S. Manager', 7 from dual union all
select 'India', 'S.Analyst', 10 from dual union all
select 'USA', 'Analyst', 1 from dual union all
select 'USA', 'S. Analyst', 2 from dual union all
select 'USA', 'Partner', 2 from dual union all
select 'Europe', 'A. Worker', 1 from dual union all
select 'Europe', 'B. Worker', 2 from dual union all
select 'Europe', 'C. Worker', 3 from dual union all
select 'Europe', 'D. Worker', 4 from dual union all
select 'Europe', 'E. Worker', 5 from dual union all
select 'Europe', 'F. Worker', 6 from dual union all
select 'Europe', 'G. Worker', 7 from dual union all
select 'China', 'A. Worker', 1 from dual union all
select 'China', 'B. Worker', 1 from dual union all
select 'China', 'C. Worker', 5 from dual union all
select 'China', 'D. Worker', 5 from dual;
20 rows affected

with 
  numbered (region, role, employees, rn) as
  (
    select
      region, role, employees,
      row_number() over (partition by region order by employees, role)
    from mytable
  ),
  grouped (region, roles, employees, grp, rn) as
  (
    select region, role, employees, 1, rn from numbered where rn = 1
    union all
    select 
      n.region,
      case when g.employees >= 6 
          then n.role
          else g.roles || ', ' || n.role
      end,
      case when g.employees >= 6 
           then n.employees
           else n.employees + g.employees
      end,
      case when g.employees >= 6 
           then g.grp + 1
           else g.grp
      end,
      n.rn
    from grouped g
    join numbered n on n.region = g.region and n.rn = g.rn + 1
  )
select
  region, max(roles), max(employees),
  case when max(employees) >= 6
       then 'complete'
       else 'incomplete'
  end as complete
from grouped
group by region, grp
order by region, grp;


REGION
MAX(ROLES)
MAX(EMPLOYEES)
COMPLETE


Africa
Analyst, S. Analyst, Manager
14
complete

China
A. Worker, B. Worker, C. Worker
7
complete

China
D. Worker
5
incomplete

Europe
A. Worker, B. Worker, C. Worker
6
complete

Europe
D. Worker, E. Worker
9
complete

Europe
F. Worker
6
complete

Europe
G. Worker
7
complete

India
Partner, S. Manager
9
complete

India
S.Analyst
10
complete

USA
Analyst, Partner, S. Analyst
5
incomplete


SELECT region,
       LISTAGG(role, '_') WITHIN GROUP (ORDER BY total_employees) AS roles,
       SUM(employees) AS employees
FROM   mytable
MATCH_RECOGNIZE(
  PARTITION BY region
  ORDER BY employees
  MEASURES
    SUM(employees) AS total_employees
  ALL ROWS PER MATCH
  PATTERN (^ employees*? at_least_6_employees)
  DEFINE
    at_least_6_employees AS SUM(employees) >= 6
)
GROUP BY
       region;


REGION
ROLES
EMPLOYEES


Africa
Analyst_S. Analyst_Manager
14

China
B. Worker_A. Worker_C. Worker
7

Europe
A. Worker_B. Worker_C. Worker
6

India
Partner_S. Manager
9

REGION	MAX(ROLES)	MAX(EMPLOYEES)	COMPLETE
Africa	Analyst, S. Analyst, Manager	14	complete
China	A. Worker, B. Worker, C. Worker	7	complete
China	D. Worker	5	incomplete
Europe	A. Worker, B. Worker, C. Worker	6	complete
Europe	D. Worker, E. Worker	9	complete
Europe	F. Worker	6	complete
Europe	G. Worker	7	complete
India	Partner, S. Manager	9	complete
India	S.Analyst	10	complete
USA	Analyst, Partner, S. Analyst	5	incomplete

REGION	ROLES	EMPLOYEES
Africa	Analyst_S. Analyst_Manager	14
China	B. Worker_A. Worker_C. Worker	7
Europe	A. Worker_B. Worker_C. Worker	6
India	Partner_S. Manager	9