1  with CARD (DEP_ID,ID,STATUS) as (
  2  select 1,100,'Z' from dual union all
  3  select 1,200,'Рабочая' from dual union all
  4  select 1,300,'V' from dual union all
  5  select 2,102,'Z' from dual union all
  6  select 2,202,'A' from dual union all
  7  select 2,302,'V' from dual)
  8  select dep_id,max(decode(status,'Рабочая','Рабочая','Архив')) s
  9  from card
 10* group by dep_id
SQL> /

    DEP_ID S
---------- -------
         1 Рабочая
         2 Архив

SQL> ed
Wrote file afiedt.buf

  1  with CARD (DEP_ID,ID,STATUS) as (
  2  select 1,100,'Z' from dual union all
  3  select 1,200,'Рабочая' from dual union all
  4  select 1,300,'V' from dual union all
  5  select 2,102,'Z' from dual union all
  6  select 2,202,'A' from dual union all
  7  select 2,302,'V' from dual)
  8  select *
  9* from card
SQL> /

    DEP_ID         ID STATUS
---------- ---------- -------
         1        100 Z
         1        200 Рабочая
         1        300 V
         2        102 Z
         2        202 A
         2        302 V

6 rows selected.