SQL Pivot Column Explanation

Question

Input Data:

Occupation	Name
Doctor	Smith
Doctor	George
Professor	Baker
Singer	Davis
Singer	Head
Actor	Miller

Query:

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;

Result

Doctor	Professor	Singer	Actor
George	Baker	Davis	Miller
Smith	NULL	Head	NULL

My question is if an aggregate function is being used like max, how am I getting multiple values for occupations?

I tried removing row number and searching if row number was playing any part in this And I am still not sure

Answer 1

With the ROW_NUMBER you can define the order of the result set.

Of course you could achieve this, by changing he order in the row_number window function

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

fiddle