SELECT
    PF_CODE AS 都道府県コード
    ,PF_NAME AS 都道府県名
    ,MAX(CASE S.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
    ,MAX(CASE S.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    ,MAX(CASE S.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
    ,MAX(CASE S.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    ,MAX(CASE S.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
    ,MAX(CASE S.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    ,SUM(AMT) AS 合計人数
    
FROM
    (
    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 F INNER JOIN PREFECTURE P ON F.PF_CODE = P.PF_CODE
        INNER JOIN NATIONALITY N ON  F.NATION_CODE = N.NATION_CODE
        WHERE F.NATION_CODE != 113
     ) AS S
    
GROUP BY S.PF_CODE,S.PF_NAME
ORDER BY 合計人数 DESC,都道府県コード
;