WITH
data as (
    SELECT
        f.pf_code
        ,p.pf_name
    --    ,f.nation_code
        ,n.nation_name
        ,row_number() over (PARTITION BY f.pf_code ORDER BY f.amt DESC) as num
        ,f.amt
    FROM FOREIGNER f
    LEFT JOIN NATIONALITY n
        ON n.nation_code = f.nation_code
    LEFT JOIN PREFECTURE p
        ON p.pf_code = f.pf_code
    WHERE 1 = 1
        and f.nation_code IS NOT '113'
)

SELECT
    t.pf_code as '都道府県コード'
    ,t.pf_name as '都道府県名'
    ,t1.nation_name as '1位 国名'
    ,t1.amt as '1位 人数'
    ,t2.nation_name as '2位 国名'
    ,t2.amt as '2位 人数'
    ,t3.nation_name as '3位 国名'
    ,t3.amt as '3位 人数'
    ,sum(t.amt) as '合計人数'
FROM data t
JOIN data t1
    ON t.pf_code = t1.pf_code
    and t1.num = 1
JOIN data t2
    ON t.pf_code = t2.pf_code
    and t2.num = 2
JOIN data t3
    ON t.pf_code = t3.pf_code
    and t3.num = 3
GROUP BY
    t.pf_code
ORDER BY
    9 DESC
    ,1 ASC