with k AS (SELECT FO.PF_CODE AS PF_CODE
                  ,PF_NAME AS PF_NAME
                  ,FO.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 asc) 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 FO.NATION_CODE <> '113')

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

FROM k
GROUP BY PF_CODE ,PF_NAME
ORDER BY 合計人数 desc
,都道府県コード asc