How to PIVOT columns based on the date

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

INSERT INTO region VALUES('Paris','23-01-15');
INSERT INTO region VALUES('Paris','28-01-15');
INSERT INTO region VALUES('France','23-01-16');
INSERT INTO region VALUES('France','22-07-17');
INSERT INTO region VALUES('France','21-01-17');
INSERT INTO region VALUES('Germany','25-01-18');

COMMIT;

I need a SELECT query where I would require DISTINCT region_name and based on the region_date. SELECT query should have 5 columns as region_name year_1 year_2 year_3 year_4 wherein year_1 is 2015 year_2 is 2016 and so on. Based on the region_name I need a COUNT for the region_date that how many regions fall in the same year. Below is my expected result

For Paris – for Year_1 – It has 2 records so the count is 2 for year_1 and for rest it is 0.
Likewise, for all. I am wondering if this can be done.

Note: I have to restrict the report till the year 2018 only. If anything comes after 2018 then that will be included in the report

Tool used: SQL Developer(18c)

>Solution :

You can use pivot to do so:

Schema and insert statements:

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

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

 INSERT INTO region VALUES('France','23-JAN-16');
 INSERT INTO region VALUES('France','22-JUL-17');
 INSERT INTO region VALUES('France','21-JAN-17');
 INSERT INTO region VALUES('Germany','25-JAN-18');

Query:

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

Output:

db<>fiddle here