with sub as ( 
    SELECT
        F.PF_CODE AS PF_CODE
        , P.PF_NAME AS PF_NAME
        , N.NATION_NAME AS NATION_NAME
        , F.AMT AS AMT
        , RANK() OVER ( 
            PARTITION BY
                F.PF_CODE 
            ORDER BY
                AMT DESC
                , F.NATION_CODE
        ) AS RANK                           
    FROM
        FOREIGNER AS F 
        INNER JOIN NATIONALITY AS N 
            ON N.NATION_CODE = F.NATION_CODE 
        INNER JOIN PREFECTURE AS P 
            ON P.PF_CODE = F.PF_CODE 
    WHERE
        F.NATION_CODE != '113'
)

select
    PF_CODE as 都道府県コード
    , PF_NAME as 都道府県名
    , max(case sub.RANK when 1 then NATION_NAME else NULL end) as "1位 国名"
    , max(case sub.RANK when 1 then AMT else 0 end) as "1位 人数"
    , max(case sub.RANK when 2 then NATION_NAME else NULL end) as "2位 国名"
    , max(case sub.RANK when 2 then AMT else 0 end) as "2位 人数"
    , max(case sub.RANK when 3 then NATION_NAME else NULL end) as "3位 国名"
    , max(case sub.RANK when 3 then AMT else 0 end) as "3位 人数"
    , sum(AMT) as 合計人数
from
    sub
group by
    PF_CODE
order by
    合計人数 desc
    , 都道府県コード
;