SELECT
    Y.PF_CODE           AS 都道府県コード
    , P.PF_NAME         AS 都道府県名
    , N1.NATION_NAME    AS '1位 国名'
    , Y.AMT1            AS '1位 人数'
    , N2.NATION_NAME    AS '2位 国名'
    , Y.AMT2            AS '2位 人数'
    , N3.NATION_NAME    AS '3位 国名'
    , Y.AMT3            AS '3位 人数'
    , Y.AMT             AS 合計人数
FROM
(
    SELECT
        X.PF_CODE
        , MAX(
            CASE
                WHEN
                    X.RANK = 1
                    THEN
                        X.NATION_CODE
                    ELSE
                        NULL
            END
        ) AS NATION_CODE_1
        , MAX(
            CASE
                WHEN
                    X.RANK = 1
                    THEN
                        X.AMT
                    ELSE
                        NULL
            END
        ) AS AMT1
        , MAX(
            CASE
                WHEN
                    X.RANK = 2
                    THEN
                        X.NATION_CODE
                    ELSE
                        NULL
            END
        ) AS NATION_CODE_2
        , MAX(
            CASE
                WHEN
                    X.RANK = 2
                    THEN
                        X.AMT
                    ELSE
                        NULL
            END
        ) AS AMT2
        , MAX(
            CASE
                WHEN
                    X.RANK = 3
                    THEN
                        X.NATION_CODE
                    ELSE
                        NULL
            END
        ) AS NATION_CODE_3
        , MAX(
            CASE
                WHEN
                    X.RANK = 3
                    THEN
                        X.AMT
                    ELSE
                        NULL
            END
        ) AS AMT3
        , SUM(AMT) AS AMT
    FROM
        (
            SELECT
                F.PF_CODE
                , RANK() OVER (PARTITION BY F.PF_CODE ORDER BY SUM(AMT) DESC) AS RANK
                , F.NATION_CODE
                , SUM(AMT) AS AMT
            FROM
                FOREIGNER AS F
            WHERE
                1=1
                AND F.NATION_CODE NOT IN ('113')
            GROUP BY
                F.PF_CODE
                , F.NATION_CODE
        ) AS X
    GROUP BY
        X.PF_CODE
) Y
LEFT OUTER JOIN
    PREFECTURE AS P
    ON 1=1
    AND Y.PF_CODE = P.PF_CODE
LEFT OUTER JOIN
    NATIONALITY AS N1
    ON 1=1
    AND Y.NATION_CODE_1 = N1.NATION_CODE
LEFT OUTER JOIN
    NATIONALITY AS N2
    ON 1=1
    AND Y.NATION_CODE_2 = N2.NATION_CODE
LEFT OUTER JOIN
    NATIONALITY AS N3
    ON 1=1
    AND Y.NATION_CODE_3 = N3.NATION_CODE
ORDER BY
    Y.AMT DESC
    , Y.PF_CODE