with temp1 as(
    select
        PF_CODE, 
        NATION_CODE,
        RANK() OVER(PARTITION BY PF_CODE ORDER BY amt desc, NATION_CODE asc) as rank,
        amt
    from FOREIGNER
    where NATION_CODE != '113'
),
temp2 as(
    select
        t.PF_CODE as '都道府県コード', 
        p.PF_NAME as '都道府県名',
        sum(
            case when rank = 1 then cast(NATION_CODE as integer) else 0 end 
        ) as NATION_CODE_1, 
        sum(
            case when rank = 1 then amt else 0 end 
        ) as amt_1, 
        sum(
            case when rank = 2 then cast(NATION_CODE as integer) else 0 end 
        ) as NATION_CODE_2, 
        sum(
            case when rank = 2 then amt else 0 end 
        ) as amt_2, 
        sum(
            case when rank = 3 then cast(NATION_CODE as integer) else 0 end 
        ) as NATION_CODE_3, 
        sum(
            case when rank = 3 then amt else 0 end 
        ) as amt_3, 
        sum(amt) as 'sum'
    from temp1 as t
    where rank in (1, 2, 3)
)
select 
from temp2
    inner join NATIONALITY as n on t.NATION_CODE = n.NATION_CODE
    inner join PREFECTURE as p on t.PF_CODE = p.PF_CODE
;