Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (col1, col2) AS
SELECT 'id1', 1 FROM DUAL UNION ALL
SELECT 'id1', 2 FROM DUAL UNION ALL
SELECT 'id2', 1 FROM DUAL UNION ALL
SELECT 'id2', 2 FROM DUAL UNION ALL
SELECT 'id3', 1 FROM DUAL UNION ALL
SELECT 'id3', 2 FROM DUAL;
6 rows affected

SELECT LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1) AS col1,
       LISTAGG(col2, ',') WITHIN GROUP (ORDER BY col2) AS col2,
       GROUPING_ID(col1, col2) AS grp
FROM   table_name
GROUP BY CUBE(col1, col2)
ORDER BY grp, col1, col2


COL1
COL2
GRP


id1
1
0

id1
2
0

id2
1
0

id2
2
0

id3
1
0

id3
2
0

id1,id1
1,2
1

id2,id2
1,2
1

id3,id3
1,2
1

id1,id2,id3
1,1,1
2

id1,id2,id3
2,2,2
2

id1,id1,id2,id2,id3,id3
1,1,1,2,2,2
3


CREATE TABLE table2 (col1, col2, col3) AS
SELECT t1.COLUMN_VALUE,
       t2.COLUMN_VALUE,
       t3.COLUMN_VALUE
FROM   TABLE(SYS.ODCIVARCHAR2LIST('id1', 'id2', 'id3')) t1
       CROSS JOIN TABLE(SYS.ODCINUMBERLIST(1, 2)) t2
       CROSS JOIN TABLE(SYS.ODCINUMBERLIST(3, 4)) t3;
12 rows affected

SELECT LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1) AS col1,
       LISTAGG(col2, ',') WITHIN GROUP (ORDER BY col2) AS col2,
       LISTAGG(col3, ',') WITHIN GROUP (ORDER BY col3) AS col3,
       GROUPING_ID(col1, col2, col3) AS grp
FROM   table2
GROUP BY CUBE(col1, col2, col3)
ORDER BY grp, col1, col2, col3


COL1
COL2
COL3
GRP


id1
1
3
0

id1
1
4
0

id1
2
3
0

id1
2
4
0

id2
1
3
0

id2
1
4
0

id2
2
3
0

id2
2
4
0

id3
1
3
0

id3
1
4
0

id3
2
3
0

id3
2
4
0

id1,id1
1,1
3,4
1

id1,id1
2,2
3,4
1

id2,id2
1,1
3,4
1

id2,id2
2,2
3,4
1

id3,id3
1,1
3,4
1

id3,id3
2,2
3,4
1

id1,id1
1,2
3,3
2

id1,id1
1,2
4,4
2

id2,id2
1,2
3,3
2

id2,id2
1,2
4,4
2

id3,id3
1,2
3,3
2

id3,id3
1,2
4,4
2

id1,id1,id1,id1
1,1,2,2
3,3,4,4
3

id2,id2,id2,id2
1,1,2,2
3,3,4,4
3

id3,id3,id3,id3
1,1,2,2
3,3,4,4
3

id1,id2,id3
1,1,1
3,3,3
4

id1,id2,id3
1,1,1
4,4,4
4

id1,id2,id3
2,2,2
3,3,3
4

id1,id2,id3
2,2,2
4,4,4
4

id1,id1,id2,id2,id3,id3
1,1,1,1,1,1
3,3,3,4,4,4
5

id1,id1,id2,id2,id3,id3
2,2,2,2,2,2
3,3,3,4,4,4
5

id1,id1,id2,id2,id3,id3
1,1,1,2,2,2
3,3,3,3,3,3
6

id1,id1,id2,id2,id3,id3
1,1,1,2,2,2
4,4,4,4,4,4
6

id1,id1,id1,id1,id2,id2,id2,id2,id3,id3,id3,id3
1,1,1,1,1,1,2,2,2,2,2,2
3,3,3,3,3,3,4,4,4,4,4,4
7
COL1	COL2	GRP
id1	1	0
id1	2	0
id2	1	0
id2	2	0
id3	1	0
id3	2	0
id1,id1	1,2	1
id2,id2	1,2	1
id3,id3	1,2	1
id1,id2,id3	1,1,1	2
id1,id2,id3	2,2,2	2
id1,id1,id2,id2,id3,id3	1,1,1,2,2,2	3