Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test(
    Num NVARCHAR2(50) NULL,
    color NVARCHAR2(50) NULL
);
insert all
into test values('A','紅、黃')
into test values('B','黑')
into test values('C','黑')
into test values('D','紅、黃、黑')
select * from dual;
4 rows affected

select Num,color
from test;


NUM
COLOR


A
紅、黃

B
黑

C
黑

D
紅、黃、黑


SELECT *
FROM (
    SELECT Num, regexp_substr(color, '[^、]+', 1, level) color,'Y' Agg
    FROM (SELECT Num, color FROM test)
    CONNECT BY instr(color, '、', 1, level - 1) > 0
) t
PIVOT (
	MAX(Agg)
	FOR color IN ('紅' 紅, '黃' 黃, '黑' 黑)
) p


NUM
紅
黃
黑


A
Y
Y
null

D
Y
Y
Y

B
null
null
Y

C
null
null
Y

NUM	紅	黃	黑
A	Y	Y	null
D	Y	Y	Y
B	null	null	Y
C	null	null	Y