Oracle 18c

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

create table commasplit (agentname varchar(40), categories varchar(100));
insert into commasplit(agentname,categories) VALUES('Alice', 'cat01, cat02, cat03')
1 rows affected

insert into commasplit(agentname,categories) VALUES('Bob', 'cat04, cat05')
1 rows affected

insert into commasplit(agentname,categories) VALUES('Carl', 'cat02, cat06, cat01, cat11')
1 rows affected

SELECT agentname
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 1)    AS CATA
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 2)    AS CATB  
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 3)    AS CATC 
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 4)    AS CATD
FROM commasplit;


AGENTNAME
CATA
CATB
CATC
CATD


Alice
cat01
 cat02
 cat03
null

Bob
cat04
 cat05
null
null

Carl
cat02
 cat06
 cat01
 cat11

AGENTNAME	CATA	CATB	CATC	CATD
Alice	cat01	cat02	cat03	null
Bob	cat04	cat05	null	null
Carl	cat02	cat06	cat01	cat11