SELECT PF_CODE AS 都道府県コード
,PF_NAME AS 都道府県名
,MAX(CASE K.LANK WHEN 1 THEN NATION_NAME ELSE NULL END ) AS '1位 国名'
,MAX(CASE K.LANK WHEN 1 THEN AMT ELSE 0 END ) AS '1位 人数'
,MAX(CASE K.LANK WHEN 2 THEN NATION_NAME ELSE NULL END ) AS '2位 国名'
,MAX(CASE K.LANK WHEN 2 THEN AMT ELSE 0 END ) AS '2位 人数'
,MAX(CASE K.LANK WHEN 3 THEN NATION_NAME ELSE NULL END ) AS '3位 国名'
,MAX(CASE K.LANK WHEN 3 THEN AMT ELSE 0 END ) AS '3位 人数'
,SUM(AMT) AS '合計人数'
FROM 
           (SELECT F.PF_CODE AS PF_CODE
                   ,P.PF_NAME AS PF_NAME
                   ,F.NATION_CODE AS NATION_CODE
                   ,F.AMT AS AMT
                   ,N.NATION_NAME AS NATION_NAME
                   ,P.PF_NAME AS PF_NAME
                   ,RANK() OVER(PARTITION BY F.PF_CODE ORDER BY AMT DESC,F.NATION_CODE ASC) AS LANK
            FROM FOREIGNER AS F
            INNER JOIN PREFECTURE AS P
            ON F.PF_CODE = P.PF_CODE
            INNER JOIN NATIONALITY AS N
            ON F.NATION_CODE=N.NATION_CODE
            WHERE F.NATION_CODE<>'113'
            ) AS K
GROUP BY K.PF_CODE
,K.PF_NAME 
ORDER BY 合計人数 desc,
都道府県コード asc;