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 |
Advertisement
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