HackerRank SQL: Occupations

30 September 2021 - less than 1 min read time
Tags: SQL TIL

Occupations:

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.

데이터프레임의 행과 열을 바꾸라는 문제입니다.

Oracle [출처]: https://yurimyurim.tistory.com/11

SELECT min(decode(occupation,'Doctor', Name)) Doctor /*occupation이 doctor일 경우 name을 return하라 그 중 min값을 불러와라(RN의 min값 즉 alphabet정렬 한것 중 min값)*/
       ,min(decode(occupation,'Professor', Name)) Professor
       ,min(decode(occupation,'Singer', Name)) Singer
       ,min(decode(occupation, 'Actor', Name)) Actor
       From(
           select occupation, name
           , Row_number() over(partition by occupation order by name) RN /*occupation으로 그룹 후 name으로 정렬해준 후 row_number를 붙여준다(각 occupation안에서 name으로 정렬 후 row_number를 붙여주는 형태)*/
           from occupations
       )
  group by rn /*row number로 그룹 후*/
  order by 1,2,3,4 /*알파벳 순서로 정렬 해준다*/;