SELECT
    PF_CODE as '都道府県コード',
    PF_NAME as '都道府県名',
    MAX(CASE WHEN r = 1 THEN NATION_NAME ELSE null END) as '1位 国名',
    MAX(CASE WHEN r = 1 THEN AMT ELSE null END) as '1位 人数',
    MAX(CASE WHEN r = 2 THEN NATION_NAME ELSE null END) as '2位 国名',
    MAX(CASE WHEN r = 2 THEN AMT ELSE null END) as '2位 人数',
    MAX(CASE WHEN r = 3 THEN NATION_NAME ELSE null END) as '3位 国名',
    MAX(CASE WHEN r = 3 THEN AMT ELSE null END) as '3位 人数',
    SUM(AMT) as '合計人数'
FROM (
    SELECT
        p.PF_CODE,
        p.PF_NAME,
        n.NATION_NAME,
        RANK() OVER (PARTITION BY p.PF_CODE ORDER BY AMT DESC) as r,
        AMT
    FROM FOREIGNER as f
    INNER JOIN NATIONALITY as n ON f.NATION_CODE = n.NATION_CODE
    INNER JOIN PREFECTURE as p ON f.PF_CODE = p.PF_CODE
    WHERE n.NATION_CODE != 113
) 
GROUP BY 1,2
ORDER BY 9 DESC, 1