By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_1 (code, value) AS
SELECT 0, 'None' FROM DUAL UNION ALL
SELECT 1, 'R' FROM DUAL UNION ALL
SELECT 2, 'W' FROM DUAL UNION ALL
SELECT 4, 'C' FROM DUAL UNION ALL
SELECT 8, 'D' FROM DUAL UNION ALL
SELECT 16, 'U' FROM DUAL UNION ALL
SELECT 32, 'Uown' FROM DUAL;
7 rows affected
CREATE TABLE Table_2 ("NUMBER") AS
SELECT COLUMN_VALUE
FROM SYS.ODCINUMBERLIST(
0,1,2,3,4,5,8,12,13,16,20,25,26,27,32,43,44,45,60,61,62,63,64,68,70
);
25 rows affected
SELECT t2."NUMBER",
CASE SUM(t1.code)
WHEN t2."NUMBER"
THEN LISTAGG(t1.code, ',') WITHIN GROUP (ORDER BY t1.code DESC)
END AS output,
CASE SUM(t1.code)
WHEN t2."NUMBER"
THEN LISTAGG(t1.value, ',') WITHIN GROUP (ORDER BY t1.code DESC)
END AS value_output
FROM table_2 t2
INNER JOIN table_1 t1
ON ( t2."NUMBER" = t1.code
OR (t1.code > 0 AND BITAND(t2."NUMBER", t1.code) = t1.code))
GROUP BY t2."NUMBER"
| NUMBER | OUTPUT | VALUE_OUTPUT |
|---|---|---|
| 0 | 0 | None |
| 1 | 1 | R |
| 2 | 2 | W |
| 3 | 2,1 | W,R |
| 4 | 4 | C |
| 5 | 4,1 | C,R |
| 8 | 8 | D |
| 12 | 8,4 | D,C |
| 13 | 8,4,1 | D,C,R |
| 16 | 16 | U |
| 20 | 16,4 | U,C |
| 25 | 16,8,1 | U,D,R |
| 26 | 16,8,2 | U,D,W |
| 27 | 16,8,2,1 | U,D,W,R |
| 32 | 32 | Uown |
| 43 | 32,8,2,1 | Uown,D,W,R |
| 44 | 32,8,4 | Uown,D,C |
| 45 | 32,8,4,1 | Uown,D,C,R |
| 60 | 32,16,8,4 | Uown,U,D,C |
| 61 | 32,16,8,4,1 | Uown,U,D,C,R |
| 62 | 32,16,8,4,2 | Uown,U,D,C,W |
| 63 | 32,16,8,4,2,1 | Uown,U,D,C,W,R |
| 68 | null | null |
| 70 | null | null |