SELECT 
    FR.PF_CODE AS 都道府県コード
    ,PF_NAME AS 都道府県名
    ,MAX(CASE WHEN "RANK"=1 THEN NA.NATION_NAME END) AS "1位 国名"
    ,MAX(CASE WHEN "RANK"=1 THEN FR.AMT END)  AS "1位 人数"
    ,MAX(CASE WHEN "RANK"=2 THEN NA.NATION_NAME END)  AS "2位 国名"
    ,MAX(CASE WHEN "RANK"=2 THEN FR.AMT END)  AS "2位 人数"
    ,MAX(CASE WHEN "RANK"=3 THEN NA.NATION_NAME END)  AS "3位 国名"
    ,MAX(CASE WHEN "RANK"=3 THEN FR.AMT END)  AS "3位 人数"
    ,SUM(FR.AMT) AS 合計人数
FROM FOREIGNER AS FR
INNER JOIN 
(
        SELECT
            PF_CODE
            ,NATION_CODE
            ,AMT
            ,RANK() OVER(partition by PF_CODE ORDER BY AMT DESC) AS "RANK"
        FROM FOREIGNER
        WHERE NATION_CODE <> "113"
) AS FRRANK
ON FR.PF_CODE = FRRANK.PF_CODE
AND FR.NATION_CODE = FRRANK.NATION_CODE
INNER JOIN NATIONALITY NA
ON FR.NATION_CODE = NA.NATION_CODE
INNER JOIN PREFECTURE PRE
ON FR.PF_CODE =PRE.PF_CODE
GROUP BY FR.PF_CODE
ORDER BY 合計人数 DESC,  都道府県コード ASC