MSQL - Switching rows to columns
What you are missing is a row that can be grouped by. Row_NUMBER is a good solution


CREATE TABLE OCCUPATIONS
    (Name varchar(255), Occupation varchar(255))
;

INSERT INTO OCCUPATIONS
    (Name, Occupation)
VALUES
    ('Samantha', 'Doctor'),
    ('Julia', 'Actor'),
    ('Maria', 'Actor'),
    ('Meera', 'Singer'),
    ('Ashely', 'Professor'),
    ('Ketty', 'Professor'),
    ('Christeen', 'Professor'),
    ('Jane', 'Actor'),
    ('Jenny', 'Doctor'),
    ('Priya', 'Singer')
  ;
GO




SELECT Row#,Doctor, Actor, Singer, Professor
from
(
  select Name, Occupation,
ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY name ASC) AS Row#
  from OCCUPATIONS
) d
pivot
(
  max(Name)
  for Occupation in (Doctor, Actor, Singer, Professor)
 ) piv;
GO


Row# | Doctor   | Actor | Singer | Professor
---: | :------- | :---- | :----- | :--------
   1 | Jenny    | Jane  | Meera  | Ashely   
   2 | Samantha | Julia | Priya  | Christeen
   3 | null     | Maria | null   | Ketty    




WITH CTE as (SELECT
Name, Occupation,
ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY name ASC) AS Row#
FROM OCCUPATIONS)
SELECT Row#,Doctor, Actor, Singer, Professor
from
(
  select Name, Occupation,Row#
  from CTE
) d
pivot
(
  max(Name)
  for Occupation in (Doctor, Actor, Singer, Professor)
 ) piv;
GO


Row# | Doctor   | Actor | Singer | Professor
---: | :------- | :---- | :----- | :--------
   1 | Jenny    | Jane  | Meera  | Ashely   
   2 | Samantha | Julia | Priya  | Christeen
   3 | null     | Maria | null   | Ketty    


db&lt;&gt;fiddle here