SQLize Online / PHPize Online  /  SQLtest Online

create table test( date date, `index` int ); insert into test values('2018-12-11', 1); insert into test values('2019-12-12', 1); insert into test values('2018-11-12', 3); insert into test values('2020-12-13', 10); insert into test values('2020-09-13', 54); insert into test values('2021-10-13', 32); insert into test values('2021-11-13', 12); select (select `index` from test where year(date) = '2018' order by date desc limit 1 ) as '2018', (select `index` from test where year(date) = '2019' order by date desc limit 1 ) as '2019', (select `index` from test where year(date) = '2020' order by date desc limit 1 ) as '2020', (select `index` from test where year(date) = '2021' order by date desc limit 1 ) as '2021' ; select substring_index(group_concat(`2018`), ',', -1) as `2018`, substring_index(group_concat(`2019`), ',', -1) as `2019`, substring_index(group_concat(`2020`), ',', -1) as `2020`, substring_index(group_concat(`2021`), ',', -1) as `2021` from (SELECT CASE WHEN date BETWEEN '2018-01-01' AND '2018-12-31' THEN `index` ELSE null END AS `2018`, CASE WHEN date BETWEEN '2019-01-01' AND '2019-12-31' THEN `index` ELSE null END AS `2019`, CASE WHEN date BETWEEN '2020-01-01' AND '2020-12-31' THEN `index` ELSE null END AS `2020`, CASE WHEN date BETWEEN '2021-01-01' AND '2021-12-31' THEN `index` ELSE null END AS `2021` FROM `test` GROUP BY `2018`, `2019`, `2020`, `2021`) as t;