How to Group Dates based on another variable?

I have a database for cars. Each car has one to many assignments. Each assignment has a status. The status needs to be renewed every fiscal year, so a status can be the same through many fiscal years.

I’d like to compile all the statuses with the years they were active for each car. Like this:

 ID | Status and Years
----+-----------------------------------------------
 0  | A (2020-2021), B (2021-2022)
 1  | Z (2022-2023)
 2  | A (2012-2013), Z (2013-2015)

What I have right now is this:

SELECT Id, Status, Effective_dt, Expiration_dt
FROM CAR_ASGNMT

Which produces this:

Id | Status | Effective_dt | Expiration_dt
---+--------+--------------+---------------
 0 |   A    |  28-SEP-2020 |  06-DEC-2020
 0 |   A    |  07-DEC-2020 |  28-MAR-2021
 0 |   A    |  28-MAR-2021 |  26-SEP-2021
 0 |   A    |  27-SEP-2021 |  05-DEC-2021
 0 |   B    |  06-DEC-2021 |  26-MAR-2022

How can I compile these statuses so they’re readable like the first table?

>Solution :

CREATE TABLE CAR_ASGNMT
    (ID int, STATUS varchar(1), EFFECTIVE_DT DATE, EXPIRATION_DT DATE)
;

BEGIN  
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES 
    (0, 'A', DATE '2020-09-28',DATE  '2020-12-06');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES 
    (0, 'A', DATE '2020-12-07', DATE '2021-03-28');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES 
    (0, 'A', DATE '2021-03-28', DATE '2021-09-26');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES 
    (0, 'A', DATE '2021-09-27', DATE '2021-12-05');
INSERT INTO CAR_ASGNMT (Id, Status, Effective_dt, Expiration_dt) VALUES 
    (0, 'B', DATE '2021-12-06', DATE '2022-03-26');
END;
/

1 rows affected

SELECT
    ID
  , STATUS
      || ' ('
      || TO_CHAR(MIN(EFFECTIVE_DT),'YYYY') 
      || ' - '
      || TO_CHAR(MAX(EXPIRATION_DT),'YYYY')
      || ')'
      AS STATUS_RANGE
  , MIN(EFFECTIVE_DT) MIN_DT
FROM CAR_ASGNMT
GROUP BY
    ID
  , STATUS

SELECT
    ID
  , LISTAGG(STATUS_RANGE,', ') WITHIN GROUP (ORDER BY MIN_DT) AS STATUS_YEARS
FROM (
    SELECT
        ID
      , STATUS
          || ' ('
          || TO_CHAR(MIN(EFFECTIVE_DT),'YYYY') 
          || ' - '
          || TO_CHAR(MAX(EXPIRATION_DT),'YYYY')
          || ')'
          AS STATUS_RANGE
      , MIN(EFFECTIVE_DT) MIN_DT
    FROM CAR_ASGNMT
    GROUP BY
        ID
      , STATUS
    ) D
GROUP BY ID;

fiddle