with T_S as (select pf_code,
                    NATION_CODE,
                    amt,
                    rank() over (PARTITION BY pf_code order by amt desc, NATION_CODE) as rnk
             from FOREIGNER
             where NATION_CODE <> '113'
             -- group by pf_code, nation_code
             )
select T_S.pf_code as '都道府県コード',
       PREFECTURE.PF_NAME as '都道府県名',
       max(case when rnk=1 then nation_name else '' end) as '1位 国名',
       max(case when rnk=1 then T_S.amt else 0 end) as '1位 人数',
    max(case when rnk=2 then nation_name else '' end) as '2位 国名',
        max(case when rnk=2 then T_S.amt else 0 end) as '2位 人数',
    max(case when rnk=3 then nation_name else '' end) as '3位 国名',
        max(case when rnk=3 then T_S.amt else 0 end) as '3位 人数',
    sum(T_S.amt) as '合計人数'

from T_S
inner join PREFECTURE on T_S.pf_code=PREFECTURE.pf_code
inner join NATIONALITY on NATIONALITY.nation_code=T_s.nation_code
group by T_S.pf_code,PREFECTURE.PF_NAME
order by 9 desc, 1;