Oracle 21c

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

CREATE TABLE DEPARTMENTS(Fullname VARCHAR(50), department VARCHAR(50), code VARCHAR(50));
INSERT INTO DEPARTMENTS VALUES('John Doe','Marketing','AB.marketing.1240');
1 rows affected

INSERT INTO DEPARTMENTS VALUES('John Doe','sales','AB.sales.30');
1 rows affected

INSERT INTO DEPARTMENTS VALUES('John Doe','service','AB.service.2000');
1 rows affected

INSERT INTO DEPARTMENTS VALUES('John Doe','customer','AB.customer.11023');
1 rows affected

select *
from DEPARTMENTS
pivot
(
  max(code) for department in ('Marketing','sales','service','customer')
)  pvt


FULLNAME
'Marketing'
'sales'
'service'
'customer'


John Doe
AB.marketing.1240
AB.sales.30
AB.service.2000
AB.customer.11023