[MySQL] CASE WHEN ~ THEN ~ END로 pivot table 변환

https://www.hackerrank.com/challenges/occupations/problem

Occupations | HackerRank

https://techblog-history-younghunjo1.tistory.com/159

[SQL] CASE WHEN으로 Pivot Table 만들기(HackerRank - Occupations 문제)

http://tcpschool.com/mysql/mysql_operator_flowControl

코딩교육 티씨피스쿨

 

-- 각 직업별 개수를 세기 위한 변수 설정
set @D = 0, @P = 0, @S = 0, @A = 0;

-- case when을 이용하여 pivot table 만들기
-- 집계 함수를 사용하는 이유는 group by로 묶기 위해서, null 처리를 위해서
select min(Doctor), min(Professor), min(Singer), min(Actor)
from (select 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,
             case
                  when Occupation = 'Doctor' then (@D := @D + 1)
                  when Occupation = 'Professor' then (@P := @P + 1)
                  when Occupation = 'Singer' then (@S := @S + 1)
                  when Occupation = 'Actor' then (@A := @A + 1)
             end as RowNumber
     from OCCUPATIONS
     order by Name) Sub
group by RowNumber;