SELECT
    PF_CODE AS 都道府県コード
    , PF_NAME AS 都道府県名
    , GROUP_CONCAT(CASE WHEN IDX = 1 THEN NATION_NAME ELSE "" END, "") AS "1位 国名"
    , SUM(CASE WHEN IDX = 1 THEN AMT ELSE "" END) AS "1位 人数"
    , GROUP_CONCAT(CASE WHEN IDX = 2 THEN NATION_NAME ELSE "" END, "") AS "2位 国名"
    , SUM(CASE WHEN IDX = 2 THEN AMT ELSE "" END) AS "2位 人数"
    , GROUP_CONCAT(CASE WHEN IDX = 3 THEN NATION_NAME ELSE "" END, "") AS "3位 国名"
    , SUM(CASE WHEN IDX = 3 THEN AMT ELSE "" END) AS "3位 人数"
    , SUM(AMT) AS 合計人数
FROM
    (SELECT
        ROW_NUMBER() OVER(
            PARTITION BY FRG.PF_CODE
            ORDER BY FRG.AMT DESC) AS IDX
        , FRG.PF_CODE AS PF_CODE
        , PRE.PF_NAME AS PF_NAME
        , FRG.NATION_CODE AS NATION_CODE
        , NAT.NATION_NAME AS NATION_NAME
        , FRG.AMT AS AMT
    FROM
        FOREIGNER AS FRG
        INNER JOIN NATIONALITY AS NAT
            ON NAT.NATION_CODE = FRG.NATION_CODE
        INNER JOIN PREFECTURE AS PRE
            ON PRE.PF_CODE = FRG.PF_CODE
    WHERE
        NAT.NATION_NAME != "その他"
    ORDER BY
        FRG.PF_CODE ASC
        , FRG.AMT DESC)
GROUP BY
    PF_CODE
ORDER BY
    合計人数 DESC
    , PF_CODE ASC;