WITH FOREIGNER_MD AS (
        SELECT
            fo.PF_CODE
            ,fo.NATION_CODE
            ,fo.AMT AS AMT
            ,RANK() OVER(PARTITION BY fo.PF_CODE ORDER BY fo.AMT DESC ,fo.NATION_CODE) AS '順位'
        FROM FOREIGNER fo
        INNER JOIN NATIONALITY na
            ON fo.NATION_CODE = na.NATION_CODE
        INNER JOIN PREFECTURE pr
            ON fo.PF_CODE = pr.PF_CODE
        WHERE fo.NATION_CODE <> '113'
)
SELECT
    fo.PF_CODE AS '都道府県コード'
    ,pr.PF_NAME AS '都道府県名'
    ,MAX(CASE WHEN fo.順位 = 1 THEN na.NATION_NAME ELSE '' END) AS '1位 国名'
    ,MAX(CASE WHEN fo.順位 = 1 THEN fo.AMT ELSE 0 END) AS '1位 人数'
    ,MAX(CASE WHEN fo.順位 = 2 THEN na.NATION_NAME ELSE '' END) AS '2位 国名'
    ,MAX(CASE WHEN fo.順位 = 2 THEN fo.AMT ELSE 0 END) AS '2位 人数'
    ,MAX(CASE WHEN fo.順位 = 3 THEN na.NATION_NAME ELSE '' END) AS '3位 国名'
    ,MAX(CASE WHEN fo.順位 = 3 THEN fo.AMT ELSE 0 END) AS '3位 人数'    
    ,SUM(fo.AMT) AS '合計人数'
FROM FOREIGNER_MD fo
INNER JOIN NATIONALITY na
    ON fo.NATION_CODE = na.NATION_CODE
INNER JOIN PREFECTURE pr
    ON fo.PF_CODE = pr.PF_CODE
GROUP BY
    fo.PF_CODE,
    pr.PF_NAME
ORDER BY
    合計人数 DESC
    ,fo.PF_CODE ASC;