SQL Server 2019

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

create table occupations (name varchar(20), occupation varchar(20))
insert into occupations
select 'Samantha' name, 'Doctor' occupation
		union all select 'Julia', 'Actor'
		union all select 'Maria', 'Actor'
		union all select 'Meera', 'Singer'
		union all select 'Ashely', 'Professor'
		union all select 'Ketty', 'Professor'
		union all select 'Christeen', 'Professor'
		union all select 'Jane', 'Actor'
		union all select 'Jenny', 'Doctor'
		union all select 'Priya', 'Singer'
10 rows affected

select * from occupations


name
occupation


Samantha
Doctor

Julia
Actor

Maria
Actor

Meera
Singer

Ashely
Professor

Ketty
Professor

Christeen
Professor

Jane
Actor

Jenny
Doctor

Priya
Singer


	select
		max(case when occupation = 'Doctor' then name end) doctor,
		max(case when occupation = 'Professor' then name end) professor,
		max(case when occupation = 'Singer' then name end) singer,
		max(case when occupation = 'Actor' then name end) actor
	from (
		select o.*, row_number() over(partition by occupation order by name) rn
		from occupations o
	) t
	group by rn


doctor
professor
singer
actor


Jenny
Ashely
Meera
Jane

Samantha
Christeen
Priya
Julia

null
Ketty
null
Maria


Warning: Null value is eliminated by an aggregate or other SET operation.

name	occupation
Samantha	Doctor
Julia	Actor
Maria	Actor
Meera	Singer
Ashely	Professor
Ketty	Professor
Christeen	Professor
Jane	Actor
Jenny	Doctor
Priya	Singer