with k AS (SELECT PR.PF_CODE AS PF_CODE
                  ,PF_NAME AS PF_NAME
                  ,NA.NATION_CODE AS NATION_CODE 
                  ,NATION_NAME AS NATION_NAME
                  ,AMT AS AMT
                  ,RANK() OVER(PARTITION BY FO.PF_CODE ORDER BY AMT desc,FO.NATION_CODE ) AS lank
             FROM FOREIGNER FO
            LEFT JOIN PREFECTURE PR
                   ON FO.PF_CODE = PR.PF_CODE
            LEFT JOIN NATIONALITY NA
                   ON FO.NATION_CODE =NA.NATION_CODE 
            WHERE 1=1
            AND FO.NATION_CODE <> '113'
            GROUP BY PR.PF_CODE 
                  ,PF_NAME 
                  ,NA.NATION_CODE
                  ,NATION_NAME 
                  ,AMT 
            )


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