SELECT JI.PF_CODE AS 都道府県コード
,PR.PF_NAME AS 都道府県名
,MAX(CASE JI.NUM WHEN 1 THEN JI.NATION_NAME ELSE NULL END) AS '1位 国名'
,MAX(CASE JI.NUM WHEN 1 THEN JI.AMT ELSE 0 END) AS '1位 人数'
,MAX(CASE JI.NUM WHEN 2 THEN JI.NATION_NAME ELSE NULL END) AS '2位 国名'
,MAX(CASE JI.NUM WHEN 2 THEN JI.AMT ELSE 0 END) AS '2位 人数'
,MAX(CASE JI.NUM WHEN 3 THEN JI.NATION_NAME ELSE NULL END) AS '3位 国名'
,MAX(CASE JI.NUM WHEN 3 THEN JI.AMT ELSE 0 END) AS '3位 人数'
,SUM(AMT) AS 合計人数
FROM (SELECT FOR.PF_CODE AS PF_CODE
             ,FOR.NATION_CODE AS NATION_CODE
             ,NA.NATION_NAME AS NATION_NAME
             ,SUM(FOR.AMT) AS AMT
             ,RANK() OVER(PARTITION BY
                    FOR.PF_CODE ORDER BY SUM(FOR.AMT) DESC,FOR.NATION_CODE ASC ) AS NUM
       FROM FOREIGNER AS FOR
       INNER JOIN NATIONALITY AS NA
       ON FOR.NATION_CODE = NA.NATION_CODE
       WHERE FOR.NATION_CODE <>'113'
       GROUP BY FOR.PF_CODE,FOR.NATION_CODE) AS JI
LEFT JOIN PREFECTURE AS PR
ON JI.PF_CODE =PR.PF_CODE
GROUP BY JI.PF_CODE,PR.PF_NAME
ORDER BY 合計人数 DESC
,都道府県コード ASC