SELECT PF_CODE AS 都道府県コード
      ,PF_NAME AS 都道府県名
      ,MAX(CASE SUB.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
      ,MAX(CASE SUB.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
      ,MAX(CASE SUB.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
      ,MAX(CASE SUB.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
      ,MAX(CASE SUB.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
      ,MAX(CASE SUB.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
      ,SUM(AMT) AS 合計人数
FROM  (SELECT FO.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 AMT DESC
                                       ,FO.NATION_CODE) AS RANK
    FROM FOREIGNER FO
    INNER JOIN NATIONALITY NA
            ON FO.NATION_CODE = NA.NATION_CODE
    INNER JOIN PREFECTURE PR
            ON FO.PF_CODE = PR.PF_CODE
    WHERE FO.NATION_CODE != '113'
    ) AS SUB
GROUP BY SUB.PF_CODE
        ,SUB.PF_NAME
ORDER BY 合計人数 DESC
        ,都道府県コード ASC