select
    pf_code as '都道府県コード'
    ,pf_name as '都道府県名'
    ,max(case when rank_result = 1 then nation_name end) as '1位 国名'
    ,max(case when rank_result = 1 then amt end) as '1位 人数'
    ,max(case when rank_result = 2 then nation_name end) as '2位 国名'
    ,max(case when rank_result = 2 then amt end) as '2位 人数'
    ,max(case when rank_result = 3 then nation_name end) as '3位 国名'
    ,max(case when rank_result = 3 then amt end) as '3位 人数'
    ,sum(amt) '合計人数'
from (
    SELECT 
        f.pf_code,
        p.pf_name,
        f.nation_code,
        n.nation_name,
        f.amt,
        RANK() OVER(partition by f.pf_code ORDER BY f.amt DESC, f.nation_code asc) AS rank_result
    FROM FOREIGNER as f
    join PREFECTURE as p
        on p.pf_code = f.pf_code
    join NATIONALITY as n
        on n.nation_code = f.nation_code
    where
        f.nation_code != 113
) tmp
group by
    pf_code
ORDER BY
    合計人数 DESC
    , 都道府県コード ASC;