We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
WITH RankedOccupations AS (
-- Assign a row number to each name based on their occupation and sort them alphabetically
SELECT
Name,
Occupation,
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNumber
FROM
OCCUPATIONS
)
-- Pivot the table by occupations
SELECT
COALESCE(MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), 'NULL') AS Doctor,
COALESCE(MAX(CASE WHEN Occupation = 'Professor' THEN Name END), 'NULL') AS Professor,
COALESCE(MAX(CASE WHEN Occupation = 'Singer' THEN Name END), 'NULL') AS Singer,
COALESCE(MAX(CASE WHEN Occupation = 'Actor' THEN Name END), 'NULL') AS Actor
FROM
RankedOccupations
GROUP BY
RowNumber
ORDER BY
RowNumber;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Occupations
You are viewing a single comment's thread. Return to all comments →
This approach is easy to use and understandable.
WITH RankedOccupations AS ( -- Assign a row number to each name based on their occupation and sort them alphabetically SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNumber FROM OCCUPATIONS ) -- Pivot the table by occupations SELECT COALESCE(MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), 'NULL') AS Doctor, COALESCE(MAX(CASE WHEN Occupation = 'Professor' THEN Name END), 'NULL') AS Professor, COALESCE(MAX(CASE WHEN Occupation = 'Singer' THEN Name END), 'NULL') AS Singer, COALESCE(MAX(CASE WHEN Occupation = 'Actor' THEN Name END), 'NULL') AS Actor FROM RankedOccupations GROUP BY RowNumber ORDER BY RowNumber;