select
    tmp.pf_code as `都道府県コード`,
    tp.PF_NAME as `都道府県名`,
    MAX(IIF(ord = 1, tn.NATION_NAME, NULL)) as `1位 国名`,
    MAX(IIF(ord = 1, amt, -1)) as `1位 人数`,
    MAX(IIF(ord = 2, tn.NATION_NAME, NULL)) as `2位 国名`,
    MAX(IIF(ord = 2, amt, -1)) as `2位 人数`,
    MAX(IIF(ord = 3, tn.NATION_NAME, NULL)) as `3位 国名`,
    MAX(IIF(ord = 3, amt, -1)) as `3位 人数`,
    TOTAL(amt) as `合計人数`
from
    (
        select
            tf.PF_CODE as pf_code,
            tf.NATION_CODE as nation_code,
            tf.AMT as amt,
            row_number() over (
                partition by tf.PF_CODE
                order by
                    tf.AMT DESC,
                    tf.NATION_CODE ASC
            ) as ord
        from
            FOREIGNER as tf
        where
            tf.NATION_CODE <> 113
    ) as tmp
    left join PREFECTURE as tp on tmp.pf_code = tp.PF_CODE
    left join NATIONALITY as tn on tmp.nation_code = tn.NATION_CODE
    group by tmp.pf_code
    order by `合計人数` DESC, `都道府県コード` ASC