WITH F AS (
    SELECT
        PF_CODE
        ,NATION_CODE
        ,AMT
        ,ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, PF_CODE) AS RANK_AMT
    FROM
        FOREIGNER
    WHERE
    NATION_CODE != '113'
)
SELECT
    F.PF_CODE AS '都道府県コード'
    ,PF_NAME AS '都道府県名'
    ,MAX(CASE WHEN RANK_AMT = 1 THEN NATION_NAME END) '1位 国名'
    ,MAX(CASE WHEN RANK_AMT = 1 THEN AMT ELSE 0 END) AS '1位 人数'
    ,MAX(CASE WHEN RANK_AMT = 2 THEN NATION_NAME END) '2位 国名'
    ,MAX(CASE WHEN RANK_AMT = 2 THEN AMT ELSE 0 END) AS '2位 人数'
    ,MAX(CASE WHEN RANK_AMT = 3 THEN NATION_NAME END) '3位 国名'
    ,MAX(CASE WHEN RANK_AMT = 3 THEN AMT ELSE 0 END) AS '3位 人数'
    ,SUM(AMT) AS '合計人数'
    -- *
FROM
    F
LEFT JOIN 
    NATIONALITY	AS N
ON F.NATION_CODE = N.NATION_CODE
LEFT JOIN
    PREFECTURE AS P
ON F.PF_CODE = P.PF_CODE
GROUP BY
    PF_NAME
ORDER BY
    合計人数 DESC, 都道府県コード