[hackerrank] Occupations
[hackerrank] Prepare > SQL > Advanced Select > 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.
Note: Print NULL when there are no more names corresponding to an occupation.
Input Format
The OCCUPATIONS table is described as follows:
Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.
Sample Input
Sample Output
Jenny Ashley Meera Jane
Samantha Christeen Priya Julia
NULL Ketty NULL Maria
Explanation
The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.
[MYSQL]
[ORCALE]
어떻게 해야 할지 감도 못잡고 있다가
문제에 Pivot에 링크가 걸려있는 걸 보고 피봇을 이용하란 건가 싶어서
사용방법을 찾아봤다.. 잘 이해가 안된다..
--SELECT Name, Doctor,Prodessor,Singer,Actor
SELECT *
from (
select Name, Occupation
from OCCUPATIONS
)
PIVOT ( Max(Name) as name
FOR Occupation
IN ('Doctor' ,'Prodessor' , 'Singer' ,'Actor' )
);
일단 이렇게 하긴했는데 통과안됨..
위 코드의 결과값
답 찾아봄
https://yurimyurim.tistory.com/11
통과된 블로그에 나온 아웃풋값이랑 내가 작성한 쿼리랑 내용은 같은데 통과 안된거 보면..
화면에는 안나오는 테스트 케이스가 뒤에 더 있다는 얘기 ..같고..
정렬을 안해서 통과가 안된 것 같다.
SELECT Doctor,Professor,Singer,Actor
from (
select Name, Occupation,ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY NAME)
from OCCUPATIONS
)
PIVOT ( Max(Name)
FOR Occupation
IN ('Doctor' Doctor ,'Professor' Professor , 'Singer' Singer ,'Actor' Actor)
)
order by 1,2,3,4;
* 정렬 추가
* order by 1,2,3,4 -- 1,2,3,4번째 컬럼 순으로 오름차순 정렬
https://wakestand.tistory.com/309
* pivot안에 in안에 별칭이 반드시 필요..없으면 에러나네
SELECT Doctor,Professor,Singer,Actor
from (
select Name, Occupation,ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY NAME)
from OCCUPATIONS
)
PIVOT ( Max(Name)
FOR Occupation
IN ('Doctor' Doctor ,'Professor' Professor , 'Singer' Singer ,'Actor' Actor)
)
order by Doctor,Professor,Singer,Actor;