/*
Deloitte SQL Interview Question

Write a SQL query that transforms the data into a specific format. The output should display the names sorted alphabetically and organized under their corresponding occupations in separate columns.
Each row in the output should represent a unique position in the sorted list, and if there are fewer names for a particular occupation, the remaining cells should be filled with NULL.
*/

-- Schema Setup
CREATE TABLE occupation (
    name VARCHAR(20),
    occup VARCHAR(20)
);

INSERT INTO occupation (name, occup) VALUES
('samantha', 'doctor'),
('julia', 'actor'),
('maria', 'actor'),
('meera', 'singer'),
('ashely', 'professor'),
('ketty', 'professor'),
('christeen', 'professor'),
('jane', 'actor'),
('jenny', 'doctor'),
('priya', 'singer');

-- Solution
SELECT
    MAX(CASE WHEN occup = 'actor' THEN name ELSE null END) AS actor,
    MAX(CASE WHEN occup = 'doctor' THEN name ELSE null END) AS doctor,
    MAX(CASE WHEN occup = 'professor' THEN name ELSE null END) AS professor,
    MAX(CASE WHEN occup = 'singer' THEN name ELSE null END) AS singer
FROM
    (SELECT
       *,
       ROW_NUMBER() OVER(PARTITION BY occup ORDER BY name) AS rn
     FROM occupation) s
GROUP BY rn
ORDER BY rn;