WITH K AS (SELECT PR.PF_CODE AS PF_CODE
                  ,PR.PF_NAME AS PF_NAME
                  ,NA.NATION_NAME AS NATION_NAME
                  ,FO.AMT AS AMT
                  ,RANK() OVER(PARTITION BY FO.PF_CODE ORDER BY FO.AMT desc,FO.NATION_CODE asc) AS LANK
              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 PR.PF_CODE 
                  ,PR.PF_NAME 
                  ,NA.NATION_NAME
                  ,FO.AMT
            )

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