[MYSQL] Hackerrank - Occupations

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true 

Occupations | HackerRank

문제

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

대강 해석

각 직업별 직업을 가진 사람의 이름을 알파벳 순서대로 정렬하여 세로로 나열하여 보여주세요.

더 이상 나열할 이름이 없으면 NULL을 출력 해주세요.

필요한 기능

• row_number
  • 행 번호를 붙여준다.
  • row_number() over (partition by 묶을 기준 컬럼 order by 정렬 할 것)

코드

SELECT 
    MAX(CASE WHEN occ.OCCUPATION = 'Doctor' THEN occ.NAME END) AS 'Doctor',
    MAX(CASE WHEN occ.OCCUPATION = 'Professor' THEN occ.NAME END) AS 'Professor',
    MAX(CASE WHEN occ.OCCUPATION = 'Singer' THEN occ.NAME END) AS 'Singer',
    MAX(CASE WHEN occ.OCCUPATION = 'Actor' THEN occ.NAME END) AS 'Actor'
FROM(
        SELECT Name, Occupation, 
            row_number() over (partition by Occupation Order by Name) as rn
        FROM Occupations
    ) as occ
GROUP BY occ.rn
ORDER BY occ.rn;