with base as(
select
    A.PF_CODE as 都道府県コード
,   C.PF_NAME as 都道府県名
,   B.NATION_NAME as 国名
,   A.AMT as 人数
,   RANK () OVER (partition by A.PF_CODE,B.NATION_CODE order by A.AMT )  as 順位
from
    FOREIGNER A
,   NATIONALITY B
,   PREFECTURE C
where
    A.NATION_CODE = B.NATION_CODE    
and A.PF_CODE = C.PF_CODE
and A.NATION_CODE <> 113
),
goukei as(
select
    A.都道府県コード
,   SUM(A.人数) as 合計人数
from
    base A
group by
    A.都道府県コード
)
select
    A.都道府県コード
,   A.都道府県名
,   case when A.順位 = 1 then 国名 else 0 end as '1位 国名'
,   case when A.順位 = 1 then 人数 else 0 end as '1位 人数'
,   case when A.順位 = 2 then 国名 else 0 end as '2位 国名'
,   case when A.順位 = 2 then 人数 else 0 end as '2位 人数'
,   case when A.順位 = 3 then 国名 else 0 end as '3位 国名'
,   case when A.順位 = 3 then 人数 else 0 end as '3位 人数'
,   B.合計人数
from
    base A
,   goukei B
order by
    B.合計人数 DESC
,   A.都道府県コード