select 
    case 
        when code = number_1 
        then number_1=code
        when count(code)>=count(number_1)
        then number_1 = sum(code)
        else 'Null'
    end
from table_1, table_2 
ORDER BY code, number_1 ;

table 1

Table 2

expected output is

CodePudding user response：

You can use the BITAND function in the JOIN condition:

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"

Which, for the sample data:

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;

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
       );

Outputs:

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

db<>fiddle here