Oracle 18c

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

create table investigator (name, title_name) as
select 'Abu', 'AP' from dual
union all
select 'Abu', 'AR' from dual
union all
select 'Abu', 'AS' from dual
union all
select 'Gyu', 'AP' from dual
union all
select 'Cu', 'TA' from dual
5 rows affected

SELECT a1.name, a1.title_name FROM INVESTIGATOR a1


NAME
TITLE_NAME


Abu
AP

Abu
AR

Abu
AS

Gyu
AP

Cu
TA


select name,
  title_name,
  dense_rank() over (partition by name order by title_name) as rnk
from investigator


NAME
TITLE_NAME
RNK


Abu
AP
1

Abu
AR
2

Abu
AS
3

Cu
TA
1

Gyu
AP
1


select *
from (
  select name,
    title_name,
    dense_rank() over (partition by name order by title_name) as rnk
  from investigator
)
pivot (
  max(title_name)
  for (rnk) in (
    1 as title1, 2 as title2, 3 as title3, 4 as title4
  )
)


NAME
TITLE1
TITLE2
TITLE3
TITLE4


Abu
AP
AR
AS
null

Cu
TA
null
null
null

Gyu
AP
null
null
null


select name,
  max(case when rnk = 1 then title_name end) as title1,
  max(case when rnk = 2 then title_name end) as title2,
  max(case when rnk = 3 then title_name end) as title3,
  max(case when rnk = 4 then title_name end) as title4
from (
  select name,
    title_name,
    dense_rank() over (partition by name order by title_name) as rnk
  from investigator
)
group by name


NAME
TITLE1
TITLE2
TITLE3
TITLE4


Abu
AP
AR
AS
null

Cu
TA
null
null
null

Gyu
AP
null
null
null

NAME	TITLE1	TITLE2	TITLE3	TITLE4
Abu	AP	AR	AS	null
Cu	TA	null	null	null
Gyu	AP	null	null	null