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.
SET @max_rows := (SELECT COUNT(DISTINCT Occupation) FROM OCCUPATIONS);
SELECT
MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer,
MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor
FROM (
SELECT
Occupation,
Name,
row_num
FROM (
SELECT
Occupation,
Name,
@row_num := IF(@current_occupation = Occupation, @row_num + 1, 1) AS row_num,
@current_occupation := Occupation
FROM
OCCUPATIONS
ORDER BY
Occupation, Name
) AS ranked
) AS pivoted
GROUP BY row_num
ORDER BY row_num;
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 →
SET @max_rows := (SELECT COUNT(DISTINCT Occupation) FROM OCCUPATIONS);
SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor FROM ( SELECT Occupation, Name, row_num FROM ( SELECT Occupation, Name, @row_num := IF(@current_occupation = Occupation, @row_num + 1, 1) AS row_num, @current_occupation := Occupation FROM OCCUPATIONS ORDER BY Occupation, Name ) AS ranked ) AS pivoted GROUP BY row_num ORDER BY row_num;