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;