SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE OCCUPATIONS (
  "Occupation" VARCHAR(9),
  "Name" VARCHAR(6)
);

INSERT INTO OCCUPATIONS
  ("Occupation", "Name")
VALUES
  ('Doctor', 'Smith'),
  ('Doctor', 'George'),
  ('Professor', 'Baker'),
  ('Singer', 'Davis'),
  ('Singer', 'Head'),
  ('Actor', 'Miller');
6 rows affected

SELECT [Doctor], [Professor], [Singer], [Actor]
FROM (
    SELECT Occupation, Name,
           ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
    FROM OCCUPATIONS
) AS SourceTable
PIVOT
(
    MAX(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor])
) AS PivotTable
ORDER BY rn;


Doctor
Professor
Singer
Actor


George
Baker
Davis
Miller

Smith
null
Head
null


SELECT [Doctor], [Professor], [Singer], [Actor]
FROM (
    SELECT Occupation, Name,
           ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
    FROM OCCUPATIONS
) AS SourceTable
PIVOT
(
    MAX(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor])
) AS PivotTable
ORDER BY rn DESC;


Doctor
Professor
Singer
Actor


Smith
null
Head
null

George
Baker
Davis
Miller