Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id NUMBER, num VARCHAR2(100), pow VARCHAR2(100))
INSERT INTO test SELECT * FROM 
      (select 1, '4,5,1', 'water,fire'     from dual union all
       select 2, '6,3,9', 'ice,water,fire' from dual
      )
2 rows affected

select * from test


ID
NUM
POW


1
4,5,1
water,fire

2
6,3,9
ice,water,fire


SELECT
    ID,
    LISTAGG(NUM, ',') WITHIN GROUP(
        ORDER BY
            NUM
    ) AS NUM,
    LISTAGG(POW, ',') WITHIN GROUP(
        ORDER BY
            POW
    ) AS POW
FROM
    (
        SELECT
            DISTINCT ROWID,
            ID,
            REGEXP_SUBSTR(NUM, '[^,]+', 1, LEVEL) NUM,
            REGEXP_SUBSTR(POW, '[^,]+', 1, LEVEL) POW
        FROM
            TEST
        CONNECT BY REGEXP_SUBSTR(NUM, '[^,]+', 1, LEVEL) IS NOT NULL
                   OR REGEXP_SUBSTR(POW, '[^,]+', 1, LEVEL) IS NOT NULL
    )
    GROUP BY ID
    ORDER BY ID;


ID
NUM
POW


1
1,4,5
fire,water

2
3,6,9
fire,ice,water