drop table samp-le purge
/
create table sample as
                select 1 category_id,1 categorization_id from dual union all
                select 2,1 from dual union all
                select 3,1 from dual union all
                select 4,2 from dual union all
                select 5,2 from dual union all
                select 6,3 from dual
/

CREATE OR REPLACE
  FUNCTION COMBINATIONS(
                        P_CNT NUMBER
                       )
    RETURN VARCHAR2 SQL_MACRO
    IS
        V_SELECT_LIST  VARCHAR2(1000) := 'SELECT ';
        V_FROM_CLAUSE  VARCHAR2(1000) := ' FROM ';
        V_WHERE_CLAUSE VARCHAR2(1000) := ' WHERE 1 = 1';
        V_N            NUMBER := 1;
    BEGIN
        FOR V_REC IN (SELECT DISTINCT CATEGORIZATION_ID FROM SAMPLE ORDER BY CATEGORIZATION_ID) LOOP
          V_SELECT_LIST  := V_SELECT_LIST || 'S' || V_N || '.CATEGORY_ID CAT' || V_N || ',';
          V_FROM_CLAUSE  := V_FROM_CLAUSE || 'SAMPLE S' || V_N || ',';
          V_WHERE_CLAUSE := V_WHERE_CLAUSE || ' AND S' || V_N || '.CATEGORIZATION_ID = ' || V_REC.CATEGORIZATION_ID;
          EXIT WHEN V_N = P_CNT;
          V_N := V_N + 1;
        END LOOP;
        RETURN RTRIM(V_SELECT_LIST,',') || RTRIM(V_FROM_CLAUSE,',') || V_WHERE_CLAUSE;
END;
/

Function created.

SQL>

SELECT * FROM COMBINATIONS((SELECT COUNT(DISTINCT CATEGORIZATION_ID) FROM SAMPLE))
/

      CAT1       CAT2       CAT3
---------- ---------- ----------
         1          4          6
         1          5          6
         2          4          6
         2          5          6
         3          4          6
         3          5          6

6 rows selected.

SQL>

INSERT
  INTO SAMPLE
  VALUES(7,4)
/
SELECT * FROM COMBINATIONS((SELECT COUNT(DISTINCT CATEGORIZATION_ID) FROM SAMPLE))
/

      CAT1       CAT2       CAT3
---------- ---------- ----------
         1          4          6
         1          5          6
         2          4          6
         2          5          6
         3          4          6
         3          5          6

6 rows selected.

SQL>

SELECT * FROM COMBINATIONS((SELECT COUNT(DISTINCT  CATEGORIZATION_ID) FROM SAMPLE))
/

      CAT1       CAT2       CAT3       CAT4
---------- ---------- ---------- ----------
         1          4          6          7
         1          5          6          7
         2          4          6          7
         2          5          6          7
         3          4          6          7
         3          5          6          7

6 rows selected.

SQL>

SQL> ROLLBACK
  2  /

Rollback complete.

SQL> SELECT * FROM COMBINATIONS((SELECT COUNT(DISTINCT  CATEGORIZATION_ID) FROM SAMPLE))
  2  /

no rows selected

SQL>