select
DAT.PF_CODE as 都道府県コード
,DAT.PF_NAME as 都道府県名
,MAX(case DAT.RK when 1 then DAT.NATION_NAME end) as "1位 国名"
,MAX(case DAT.RK when 1 then DAT.AMT end) as "1位 人数"
,MAX(case DAT.RK when 2 then DAT.NATION_NAME end) as "2位 国名"
,MAX(case DAT.RK when 2 then DAT.AMT end) as "2位 人数"
,MAX(case DAT.RK when 3 then DAT.NATION_NAME end) as "3位 国名"
,MAX(case DAT.RK when 3 then DAT.AMT end) as "3位 人数"
,SUM(DAT.AMT) as 合計人数
--,rank() over (partition by FO.NATION_CODE order by FO.AMT asc)as ranking
from (
    select
        FO.PF_CODE as PF_CODE
        ,PR.PF_NAME as PF_NAME
        ,FO.NATION_CODE as NATION_CODE
        ,NA.NATION_NAME as NATION_NAME
        ,FO.AMT as AMT
        ,rank() over (partition by FO.PF_CODE order by FO.AMT desc,FO.NATION_CODE) as RK
    from FOREIGNER FO
        inner join PREFECTURE as PR on FO.PF_CODE=PR.PF_CODE
        inner join NATIONALITY as NA on FO.NATION_CODE=NA.NATION_CODE
    where FO.NATION_CODE !=113
) as DAT
--inner join PREFECTURE PR on FO.PF_CODE=PR.PF_CODE
group by 都道府県コード
order by 合計人数 desc,DAT.NATION_CODE asc,DAT.PF_CODE asc