SELECT A.PF_CODE AS [都道府県コード]
      ,A.PF_NAME AS [都道府県名]
      ,NATION1.NATION_NAME AS [1位 国名]
      ,B.AMT1  AS [1位 人数]
      ,NATION2.NATION_NAME AS [2位 国名]
      ,B.AMT2  AS [2位 人数]
      ,NATION3.NATION_NAME AS [3位 国名]
      ,B.AMT3  AS [3位 人数]
      ,B.AMT1 + B.AMT2 + B.AMT3  AS [合計人数] 

FROM PREFECTURE A
INNER JOIN 
(
SELECT sub.PF_CODE 
      ,MAX(CASE WHEN sub.Rank = 1 THEN sub.NATION_CODE ELSE '' END) AS NATION_CODE1
      ,MAX(CASE WHEN sub.Rank = 1 THEN sub.AMT ELSE 0 END) AS AMT1
      ,MAX(CASE WHEN sub.Rank = 2 THEN sub.NATION_CODE ELSE '' END) AS NATION_CODE2
      ,MAX(CASE WHEN sub.Rank = 2 THEN sub.AMT ELSE 0 END) AS AMT2
      ,MAX(CASE WHEN sub.Rank = 3 THEN sub.NATION_CODE ELSE '' END) AS NATION_CODE3
      ,MAX(CASE WHEN sub.Rank = 3 THEN sub.AMT ELSE 0 END) AS AMT3
FROM(
SELECT a.PF_CODE
      ,a.NATION_CODE
      ,a.AMT
      ,ROW_NUMBER() OVER(PARTITION BY a.PF_CODE
                         ORDER BY a.AMT DESC
                                 ,a.NATION_CODE) AS Rank
FROM FOREIGNER a
WHERE a.NATION_CODE <> '113'
)sub
GROUP BY sub.PF_CODE
)B
ON A.PF_CODE = B.PF_CODE
INNER JOIN NATIONALITY NATION1
ON B.NATION_CODE1 = NATION1.NATION_CODE
INNER JOIN NATIONALITY NATION2
ON B.NATION_CODE2 = NATION2.NATION_CODE
INNER JOIN NATIONALITY NATION3
ON B.NATION_CODE3 = NATION3.NATION_CODE
ORDER BY B.AMT1 + B.AMT2 + B.AMT3 DESC
        ,A.PF_CODE