-- SQLite
SELECT
    t.都道府県コード AS 都道府県コード
    , t.都道府県名 AS 都道府県名
    , MAX(CASE WHEN t.RANK = 1 THEN t.国名 END) AS "1位 国名"
    , MAX(CASE WHEN t.RANK = 1 THEN t.AMT END) AS "1位 人数"
    , MAX(CASE WHEN t.RANK = 2 THEN t.国名 END) AS "2位 国名"
    , MAX(CASE WHEN t.RANK = 2 THEN t.AMT END) AS "2位 人数"
    , MAX(CASE WHEN t.RANK = 3 THEN t.国名 END) AS "3位 国名"
    , MAX(CASE WHEN t.RANK = 3 THEN t.AMT END) AS "3位 人数"
    , SUM(t.AMT) AS 合計人数
FROM
(
    SELECT
        PRF.PF_CODE AS 都道府県コード
        , PRF.PF_NAME AS 都道府県名
        , NTN.NATION_NAME AS 国名
        , FRG.AMT AS AMT
        , RANK() OVER (PARTITION BY PRF.PF_CODE ORDER BY FRG.AMT DESC) AS RANK
    FROM
        FOREIGNER AS FRG
        INNER JOIN NATIONALITY AS NTN
            ON FRG.NATION_CODE = NTN.NATION_CODE
        INNER JOIN PREFECTURE AS PRF
            ON FRG.PF_CODE = PRF.PF_CODE
    WHERE
        FRG.NATION_CODE != "113"
) AS t
GROUP BY
    都道府県コード
ORDER BY
    合計人数 DESC
    , 都道府県コード ASC
;