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


-- not generating duplicates
SELECT
    T.ID,
    REGEXP_SUBSTR(T.NUM, '[^,]+', 1, NUMS_COMMA.COLUMN_VALUE) NUM
FROM
    TEST T,
    TABLE ( CAST(MULTISET(
        SELECT
            LEVEL
        FROM
            DUAL
        CONNECT BY
            LEVEL <= LENGTH(REGEXP_REPLACE(T.NUM, '[^,]+')) + 1
    ) AS SYS.ODCINUMBERLIST) ) NUMS_COMMA


ID
NUM


1
4

1
5

1
1

2
6

2
3

2
9


-- not generating duplicates
SELECT
    T.ID,
    REGEXP_SUBSTR(T.POW, '[^,]+', 1, POW_COMMA.COLUMN_VALUE) POW
FROM
    TEST T,
    TABLE ( CAST(MULTISET(
        SELECT
            LEVEL
        FROM
            DUAL
        CONNECT BY
            LEVEL <= LENGTH(REGEXP_REPLACE(T.POW, '[^,]+')) + 1
    ) AS SYS.ODCINUMBERLIST) ) POW_COMMA


ID
POW


1
water

1
fire

2
ice

2
water

2
fire


-- Actual query
SELECT
    ID,
    LISTAGG(C_S.NUM, ',') WITHIN GROUP(
                ORDER BY
                    C_S.NUM
            ) AS NUM,
    LISTAGG(C_S.POW, ',') WITHIN GROUP(
                ORDER BY
                    C_S.POW
            ) AS POW
FROM
(SELECT
    T.ID,
    REGEXP_SUBSTR(T.NUM, '[^,]+', 1, NUMS_COMMA.COLUMN_VALUE) NUM,
    REGEXP_SUBSTR(T.POW, '[^,]+', 1, NUMS_COMMA.COLUMN_VALUE) POW
FROM
    TEST T,
    TABLE ( CAST(MULTISET(
        SELECT
            LEVEL
        FROM
            DUAL
        CONNECT BY
            LEVEL <= GREATEST(LENGTH(REGEXP_REPLACE(T.NUM, '[^,]+')),
            LENGTH(REGEXP_REPLACE(T.POW, '[^,]+'))) + 1
    ) AS SYS.ODCINUMBERLIST) ) NUMS_COMMA) C_S
 GROUP BY ID


ID
NUM
POW


1
1,4,5
fire,water

2
3,6,9
fire,ice,water