SELECT 
A.PF_CODE AS 都道府県コード
,B.PF_NAME AS 都道府県名
,MAX(case WHEN TIMES =1 THEN NATION_NAME ELSE '0' END) AS "1位 国名"
,MAX(case WHEN TIMES =1 THEN AMT ELSE 0 END) AS "1位 人数"
,MAX(case WHEN TIMES =2 THEN NATION_NAME ELSE '0' END) AS "2位 国名"
,MAX(case WHEN TIMES =2 THEN AMT ELSE 0 END) AS "2位 人数"
,MAX(case WHEN TIMES =3 THEN NATION_NAME ELSE '0' END) AS "3位 国名"
,MAX(case WHEN TIMES =3 THEN AMT ELSE 0 END) AS "3位 人数"
,MAX(case WHEN TIMES =1 THEN total_AMT ELSE 0 END) AS "合計人数"
FROM (
    SELECT FOREIGNER.PF_CODE
    ,NATION_NAME
    ,AMT
    ,ROW_NUMBER() over (PARTITION BY PF_CODE ORDER BY AMT DESC) as times
    ,SUM(AMT) OVER (PARTITION BY PF_CODE) as total_AMT
    FROM FOREIGNER
    INNER JOIN NATIONALITY C ON FOREIGNER.NATION_CODE = C.NATION_CODE
    WHERE FOREIGNER.NATION_CODE <> '113'
) A
INNER JOIN PREFECTURE B ON A.PF_CODE=B.PF_CODE
WHERE TIMES <=3
GROUP BY A.PF_CODE
order BY 9 DESC,A.PF_CODE