SELECT
    t.PF_CODE AS [都道府県コード]
    , p.PF_NAME AS [都道府県名]
    , MAX(IIF(t.PF_RANK = 1, n.NATION_NAME, '')) AS [1位 国名]
    , MAX(IIF(t.PF_RANK = 1, t.AMT, 0)) AS [1位 人数]
    , MAX(IIF(t.PF_RANK = 2, n.NATION_NAME, '')) AS [2位 国名]
    , MAX(IIF(t.PF_RANK = 2, t.AMT, 0)) AS [2位 人数]
    , MAX(IIF(t.PF_RANK = 3, n.NATION_NAME, '')) AS [3位 国名]
    , MAX(IIF(t.PF_RANK = 3, t.AMT, 0)) AS [3位 人数]
    , SUM(t.AMT) AS [合計人数]
FROM
    (
    SELECT
        PF_CODE
        , NATION_CODE
        , AMT
        , RANK () OVER (
            PARTITION BY PF_CODE
		    ORDER BY AMT DESC, NATION_CODE ASC
	    ) AS PF_RANK
    FROM
        FOREIGNER
    WHERE
        NATION_CODE <> '113'
    ) AS t
INNER JOIN
    NATIONALITY AS n
ON
    t.NATION_CODE = n.NATION_CODE
INNER JOIN
     PREFECTURE AS p
ON
    t.PF_CODE = p.PF_CODE
GROUP BY
    t.PF_CODE
ORDER BY
    [合計人数] DESC, [都道府県コード] ASC
;