WITH temp AS (
    SELECT
        a.PF_CODE
        , b.PF_NAME
        , a.NATION_CODE
        , c.NATION_NAME
        , a.AMT
        , DENSE_RANK() OVER (PARTITION BY a.PF_CODE ORDER BY a.AMT DESC, a.NATION_CODE ASC) AS r
        , SUM(AMT) OVER (PARTITION BY a.PF_CODE) AS s
        
    FROM
        FOREIGNER AS a
        INNER JOIN PREFECTURE AS b
            ON a.PF_CODE = b.PF_CODE
        INNER JOIN NATIONALITY AS c
            ON a.NATION_CODE = c.NATION_CODE AND a.NATION_CODE <> '113'
            
    GROUP BY
        1,2,3,4,5
            
)

SELECT
    a.PF_CODE AS 都道府県コード
    , a.PF_NAME AS 都道府県名
    , CASE WHEN a.r = 1 THEN a.NATION_NAME ELSE NULL END '1位 国名'
    , CASE WHEN a.r = 1 THEN a.AMT ELSE NULL END '1位 人数'
    , CASE WHEN b.r = 2 THEN b.NATION_NAME ELSE NULL END '2位 国名'
    , CASE WHEN b.r = 2 THEN b.AMT ELSE NULL END '2位 人数'
    , CASE WHEN c.r = 3 THEN c.NATION_NAME ELSE NULL END '3位 国名'
    , CASE WHEN c.r = 3 THEN c.AMT ELSE NULL END '3位 人数'
    , a.s AS 合計人数
    
FROM
    temp a
    INNER JOIN temp b
    ON a.PF_CODE = b.PF_CODE AND a.r = 1 AND b.r = 2
    INNER JOIN temp c
    ON a.PF_CODE = c.PF_CODE AND c.r = 3

WHERE a.r < 4

ORDER BY a.s DESC, a.PF_CODE