[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

https://docu94.tistory.com/72

 

통과된 블로그에 나온 아웃풋값이랑 내가 작성한 쿼리랑 내용은 같은데 통과 안된거 보면..

화면에는 안나오는 테스트 케이스가 뒤에 더 있다는 얘기 ..같고..

정렬을 안해서 통과가 안된 것 같다.

 

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;

 

 

 

 

 

---

https://kutar37.tistory.com/entry/oracle-%ED%95%A9%EA%B3%84%ED%95%A8%EC%88%98-%EC%97%86%EB%8A%94-pivot

 

https://goddaehee.tistory.com/58