/*
SELECT uid,
       max(CASE WHEN key = 'c1' THEN value END) AS c1,
       max(CASE WHEN key = 'c2' THEN value END) AS c2,
       max(CASE WHEN key = 'c3' THEN value END) AS c3
FROM vtable
GROUP BY uid
*/


SELECT 
    A.PF_CODE AS 都道府県コード,
    B.PF_NAME AS 都道府県,
    MAX (CASE WHEN SURVEY_YEAR = "2015" THEN TOTAL_AMT END) as 総人口2015年,
    MAX (CASE WHEN SURVEY_YEAR = "2020" THEN TOTAL_AMT END) as 総人口2020年,
    ROUND((MAX (CASE WHEN SURVEY_YEAR = "2020" THEN TOTAL_AMT*100 END)/MAX (CASE WHEN SURVEY_YEAR = "2015" THEN TOTAL_AMT END)),1) AS 人口増加率
FROM POPU_TRANSITION A
LEFT JOIN PREFECTURE B
ON A.PF_CODE = B.PF_CODE
GROUP BY 都道府県コード, 都道府県
HAVING  人口増加率 > 100
ORDER BY 人口増加率 DESC, A.PF_CODE ASC