SQL] Pivot quiz

엑셀의 피벗을 생각했는데 sql에서는 뭔가 다르더라. 하지만 결과물은 같다.

Hackerrank > SQL > Advanced Select > Occupations

Problem

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.
Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Name	Occupation
Samantha	Doctor
Julia	Actor
…	…

Sample Output

Jenny	Ashley	Meera	Jane
Samantha	Christeen	Priya	Julia
NULL	Ketty	NULL	Maria

Discussions 탭에 있는 답안 중 mysql에서 돌아가고, 내가 이해할만한 코드를 가져왔다.

1. 직업이 나올 때마다 지정해둔 변수@r1~4를 이용하여 카운팅 하고, RowNumber 컬럼에 입력한다.
2. 이름을 직업 별로 분류하여 해당 직업 컬럼에 입력한다.
   ... THEN Name end as ...
3. 이름을 알파벳 순으로 정렬한다.
4. FROM 여기까지 진행된 것들로부터 SELECT 직업별로 분류해둔 이름.
5. RowNumber로 GROUP BY

Solution

set @r1=0, @r2=0, @r3=0, @r4=0;

select min(Doctor), min(Professor), min(Singer), min(Actor)

from(
  select
      case when Occupation='Doctor' then (@r1:=@r1+1)
           when Occupation='Professor' then (@r2:=@r2+1)
           when Occupation='Singer' then (@r3:=@r3+1)
           when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,

      case when Occupation='Doctor' then Name end as Doctor,
      case when Occupation='Professor' then Name end as Professor,
      case when Occupation='Singer' then Name end as Singer,
      case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
) Temp
group by RowNumber

PIVOT 명령어도 있던데, 이건 mysql말고 SQL server와 Oracle에서 지원한다고 한다.