How to generate multiple combinations from tables using SQL?

I have few combinations of records in my table but i want to generate Multiple combinations from original table.

Records in table

codeset	codevalue	column	system
test1	1	null	sys1
test2	2	col2	sys1
test3	3	col3	sys1
test3	4	col4	sys1
test3	5	col5	sys1
test4	null	col6	sys1
test5	6	col7	sys2
test5	7	col8	sys2

After applied below logic and would like to get expected output.

• Group by ‘codeset’
• system name should be ‘sys1’
• ‘codevalue’ and ‘column’ should be more than 1 row
• ‘codevalue’ and ‘column’ equal rows

Expected Output

codeset	codevalue	column	system
test1	1	null	sys1
test2	2	col2	sys1
test3	3	col3	sys1
test3	3	col4	sys1
test3	3	col5	sys1
test3	4	col3	sys1
test3	4	col4	sys1
test3	4	col5	sys1
test3	5	col3	sys1
test3	5	col4	sys1
test3	5	col5	sys1
test4	null	col6	sys1
test5	6	col7	sys2
test5	7	col8	sys2

Answer

To get all possible combinations of codevalue and column for each codeset group when system has the value of sys1, you can use:

SELECT t1.codeset,
       t1.codevalue,
       NVL2(t2.codeset, t2."COLUMN", t1."COLUMN") AS "COLUMN",
       t1.system
FROM   table_name t1
       LEFT OUTER JOIN table_name t2
       ON (
           t1.codeset = t2.codeset
       AND t1.system  = 'sys1'
       AND t2.system  = 'sys1'
       )

Or, as a hierarchical query:

SELECT codeset,
       CONNECT_BY_ROOT codevalue AS codevalue,
       "COLUMN",
       system
FROM   table_name
WHERE  LEVEL <= 2
CONNECT BY NOCYCLE
       PRIOR codeset = codeset
AND    PRIOR system  = 'sys1'
AND    system = 'sys1'
AND    PRIOR "COLUMN" != "COLUMN"

Which, for the sample data:

CREATE TABLE table_name (codeset, codevalue, "COLUMN", system) AS
SELECT 'test1', 1,    null,   'sys1' FROM DUAL UNION ALL
SELECT 'test2', 2,    'col2', 'sys1' FROM DUAL UNION ALL
SELECT 'test3', 3,    'col3', 'sys1' FROM DUAL UNION ALL
SELECT 'test3', 4,    'col4', 'sys1' FROM DUAL UNION ALL
SELECT 'test3', 5,    'col5', 'sys1' FROM DUAL UNION ALL
SELECT 'test4', null, 'col6', 'sys1' FROM DUAL UNION ALL
SELECT 'test5', 6,    'col7', 'sys2' FROM DUAL UNION ALL
SELECT 'test5', 7,    'col8', 'sys2' FROM DUAL;

Both output:

CODESET	CODEVALUE	COLUMN	SYSTEM
test1	1	null	sys1
test2	2	col2	sys1
test3	3	col3	sys1
test3	4	col3	sys1
test3	5	col3	sys1
test3	3	col4	sys1
test3	4	col4	sys1
test3	5	col4	sys1
test3	3	col5	sys1
test3	4	col5	sys1
test3	5	col5	sys1
test4	null	col6	sys1
test5	6	col7	sys2
test5	7	col8	sys2

db<>fiddle here