WITH k AS(SELECT FO.PF_CODE AS PF_CODE
                 ,PR.PF_NAME AS PF_NAME
                 ,NA.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,NA.NATION_CODE asc ) AS ranq
            FROM FOREIGNER AS FO
            LEFT JOIN PREFECTURE AS PR
            ON FO.PF_CODE =PR.PF_CODE 
            LEFT JOIN NATIONALITY AS NA
            ON FO.NATION_CODE =NA.NATION_CODE 
            WHERE FO.NATION_CODE <>'113'
            GROUP BY FO.PF_CODE
                 ,PR.PF_NAME 
                 ,NA.NATION_CODE 
                 ,NA.NATION_NAME
                 ,FO.AMT 
            )

SELECT PF_CODE AS 都道府県コード
,PF_NAME AS 都道府県名
,MAX(CASE WHEN ranq=1 THEN NATION_NAME ELSE NULL END ) AS '1位 国名'
,MAX(CASE WHEN ranq=1 THEN AMT ELSE 0 END) AS '1位 人数'
,MAX(CASE WHEN ranq=2 THEN NATION_NAME ELSE NULL END ) AS '2位 国名'
,MAX(CASE WHEN ranq=2 THEN AMT ELSE 0 END) AS '2位 人数'
,MAX(CASE WHEN ranq=3 THEN NATION_NAME ELSE NULL END ) AS '3位 国名'
,MAX(CASE WHEN ranq=3 THEN AMT ELSE 0 END) AS '3位 人数'
,SUM(AMT) AS '合計人数'
FROM k
GROUP BY PF_CODE
,PF_NAME
ORDER BY 合計人数 DESC
    , 都道府県コード ASC;