SELECT A.PF_CODE 都道府県コード
        ,PF_NAME 都道府県名	
        ,MAX(CASE WHEN NUMA = 1 THEN NATION_NAME ELSE 0 END) '1位 国名'
        ,SUM(CASE WHEN NUMA = 1 THEN AMT ELSE 0 END) '1位 人数'
        ,MAX(CASE WHEN NUMA = 2 THEN NATION_NAME ELSE 0 END) '2位 国名'
        ,SUM(CASE WHEN NUMA = 2 THEN AMT ELSE 0 END) '2位 人数'
        ,MAX(CASE WHEN NUMA = 3 THEN NATION_NAME ELSE 0 END) '3位 国名'
        ,SUM(CASE WHEN NUMA = 3 THEN AMT ELSE 0 END) '3位 人数'
        ,MAX(AMTB) '合計人数'
FROM
    (SELECT 
    PF_CODE
    ,FOREIGNER.NATION_CODE
    ,NATIONALITY.NATION_NAME
    ,AMT
    ,ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC,FOREIGNER.NATION_CODE) NUMA
    ,SUM(AMT) OVER (PARTITION BY PF_CODE)  AMTB
    ,ROW_NUMBER() OVER (ORDER BY AMT DESC ) NUMB
    FROM FOREIGNER
    INNER JOIN NATIONALITY
    ON NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
    WHERE FOREIGNER.NATION_CODE <> '113') A
INNER JOIN PREFECTURE
ON PREFECTURE.PF_CODE = A.PF_CODE
GROUP BY A.PF_CODE 
        ,PF_NAME 
ORDER BY  MAX(AMTB) DESC,A.PF_CODE