medium, advanced select
https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true
생각한 풀이
SELECT
CASE WHEN occupation = "Doctor" THEN name ELSE NULL END,
CASE WHEN occupation = "Professor" THEN name ELSE NULL END,
CASE WHEN occupation = "Singer" THEN name ELSE NULL END,
CASE WHEN occupation = "Actor" THEN name ELSE NULL END
FROM occupations
다른 사람 풀이
핵심 코드
SELECT occupation, name, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name)
FROM occupations
최종 코드
SELECT
MIN(CASE WHEN occupation = "Doctor" THEN name ELSE NULL END),
MIN(CASE WHEN occupation = "Professor" THEN name ELSE NULL END),
MIN(CASE WHEN occupation = "Singer" THEN name ELSE NULL END),
MIN(CASE WHEN occupation = "Actor" THEN name ELSE NULL END)
FROM (
SELECT occupation, name, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) rn
FROM occupations) tmp
GROUP BY rn