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 JOIN PREFECTURE P ON F.PF_CODE = P.PF_CODE
        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,都道府県コード
;