Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (region, role, employees) AS
  SELECT 'Africa', 'Role 1',  2 FROM DUAL UNION ALL
  SELECT 'Africa', 'Role 2',  3 FROM DUAL UNION ALL
  SELECT 'Africa', 'Role 3',  9 FROM DUAL UNION ALL
  SELECT 'India',  'Role 1',  2 FROM DUAL UNION ALL
  SELECT 'India',  'Role 2',  7 FROM DUAL UNION ALL
  SELECT 'India',  'Role 3', 10 FROM DUAL UNION ALL
  SELECT 'USA',    'Role 1',  1 FROM DUAL UNION ALL
  SELECT 'USA',    'Role 2',  2 FROM DUAL UNION ALL
  SELECT 'USA',    'Role 3',  2 FROM DUAL;
9 rows affected

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY region
  ORDER BY employees
  MEASURES
    LISTAGG(role, '_') WITHIN GROUP (ORDER BY role) AS roles,
    SUM(employees) AS total_employees
  PATTERN (less_than_6_employees*? at_least_6_employees)
  DEFINE
    less_than_6_employees AS SUM(employees) < 6,
    at_least_6_employees AS SUM(employees) >= 6
)
ORA-62512: This aggregate is not yet supported in MATCH_RECOGNIZE clause.

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


REGION
ROLES
EMPLOYEES


India
Role 1_Role 2
9

India
Role 3
10

Africa
Role 1_Role 2_Role 3
14


SELECT region,
       LISTAGG(role, '_') WITHIN GROUP (ORDER BY role) AS roles,
       SUM(employees) AS employees
FROM   (
  SELECT region,
         role,
         employees,
         SUM(employees) OVER (PARTITION BY region ORDER BY employees, role, ROWNUM)
           AS total_employees
  FROM   table_name
)
WHERE  total_employees - employees < 6
GROUP BY region
HAVING SUM(employees) >= 6


REGION
ROLES
EMPLOYEES


Africa
Role 1_Role 2_Role 3
14

India
Role 1_Role 2
9
REGION	ROLES	EMPLOYEES
India	Role 1_Role 2	9
India	Role 3	10
Africa	Role 1_Role 2_Role 3	14