SQL Server 2019

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])
) as pvt


Fullname
Marketing
sales
service
customer


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