[SQL문제풀기] Occupations

문제

Occupations | HackerRank

Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation.

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true

MySQL

내가 작성한 오답

with occ as (select case when occupation = 'Actor' then name end Actor,
                       case when occupation = 'Doctor' then name end Doctor,
                       case when occupation = 'Professor' then name end Professor,
                       case when occupation = 'Singer' then name end Singer
                from OCCUPATIONS)
select doctor, professor, singer, actor
from occ

내가 작성한 정답

with num as (select name, occupation,
                    row_number() over(partition by occupation order by name) nn
             from OCCUPATIONS)
    ,occ as (select max(case when occupation = 'Actor' then name end) Actor,
                    max(case when occupation = 'Doctor' then name end) Doctor,
                    max(case when occupation = 'Professor' then name end) Professor,
                    max(case when occupation = 'Singer' then name end) Singer, nn
            from num
            group by nn)
select doctor, professor, singer, actor
from occ

Oracle

내가 작성한 오답 - MySql의 정답과 동일

with num as (select name, occupation,
                    row_number() over(partition by occupation order by name) nn
             from OCCUPATIONS)
    ,occ as (select max(case when occupation = 'Actor' then name end) Actor,
                    max(case when occupation = 'Doctor' then name end) Doctor,
                    max(case when occupation = 'Professor' then name end) Professor,
                    max(case when occupation = 'Singer' then name end) Singer, nn
            from num
            group by nn)
select doctor, professor, singer, actor
from occ

내가 작성한 정답

with num as (select name, occupation,
                    row_number() over(partition by occupation order by name) nn
             from OCCUPATIONS)
    ,occ as (select max(case when occupation = 'Actor' then name end) Actor,
                    max(case when occupation = 'Doctor' then name end) Doctor,
                    max(case when occupation = 'Professor' then name end) Professor,
                    max(case when occupation = 'Singer' then name end) Singer, nn
            from num
            group by nn)
select Doctor, Professor, Singer, Actor
from occ
order by nn;

PIVOT 사용한 정답

WITH num AS (
  SELECT name, occupation,
         ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS rn
  FROM OCCUPATIONS
)
SELECT Doctor, Professor, Singer, Actor
FROM num
PIVOT (
    MAX(name)
    FOR occupation IN (
        'Doctor' AS Doctor,
        'Professor' AS Professor,
        'Singer' AS Singer,
        'Actor' AS Actor
    )
)
ORDER BY rn;

MS SQL Server

with num as (select name, occupation,
                    row_number() over(partition by occupation order by name) nn
             from OCCUPATIONS)
    ,occ as (select max(case when occupation = 'Actor' then name end) Actor,
                    max(case when occupation = 'Doctor' then name end) Doctor,
                    max(case when occupation = 'Professor' then name end) Professor,
                    max(case when occupation = 'Singer' then name end) Singer, nn
            from num
            group by nn)
select doctor, professor, singer, actor
from occ;

DB2

내가 작성한 오답

select occ
from ((select name||'('||substr(occupation,1,1)||')' occ, 0 s, null o
    from OCCUPATIONS)
    union all
    (select 'There are a total of '||count(*)||' '||lower(occupation)||'s.' occ
            , 1 s, count(*) o
    from OCCUPATIONS
    group by occupation))
order by s asc, case when s=0 then occ else to_char(o) end;

내가 작성한 정답

with num as (select name, occupation,
                    row_number() over(partition by occupation order by name) nn
             from OCCUPATIONS)
    ,occ as (select max(case when occupation = 'Actor' then name end) Actor,
                    max(case when occupation = 'Doctor' then name end) Doctor,
                    max(case when occupation = 'Professor' then name end) Professor,
                    max(case when occupation = 'Singer' then name end) Singer, nn
            from num
            group by nn)
select nvl(Doctor,'NULL'), nvl(Professor,'NULL'), nvl(Singer,'NULL'), nvl(Actor,'NULL')
from occ;

정리!