Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE region (
    region_name  VARCHAR2(255),
    region_date  DATE
);

INSERT INTO region VALUES('Paris','23-JAN-15');

1 rows affected

INSERT INTO region VALUES('Paris','28-JAN-15');
1 rows affected

INSERT INTO region VALUES('France','23-JAN-16');
1 rows affected

INSERT INTO region VALUES('France','22-JUL-17');
1 rows affected

INSERT INTO region VALUES('France','21-JAN-17');
1 rows affected

INSERT INTO region VALUES('Germany','25-JAN-18');
1 rows affected

 select * from
 (SELECT region_name, extract(year from region_date)yr FROM region )
 pivot
 (
 COUNT(*) for yr  in (2015,2016,2017,2018)
 )


REGION_NAME
2015
2016
2017
2018


Paris
2
0
0
0

France
0
1
2
0

Germany
0
0
0
1