Occupations (HackerRank)

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.

풀이

제출한 답

with sub as(
	select occupation, name, rank() over (partition by occupation order by name) as rank 
	from occupations)

select 
	(case when occupation = 'Doctor' then name else null end) Doctor
from sub

-> 전체 name을 기준으로 case when 적용되어 18행 출력 / 이렇게 할 경우 없는 값에 대해서는 rank를 매길 수 없어 group by 적용할 수 없는 경우 생김

다시 제출한 답

with sub as(
	select occupation, name, rank() over (partition by occupation order by name) as rank
	from occupations)

select 
    min(case when occupation = 'Doctor' then name else null end) Doctor,
    min(case when occupation = 'Professor' then name else null end) Professor,
    min(case when occupation = 'Singer' then name else null end) Singer,
    min(case when occupation = 'Actor' then name else null end) Actor
from sub
group by rank

-> 집계함수 min을 써서 이외 null 값을 다 무시

2022.10.11