您可以PIVOT：

SELECT dept,
       r1 || '/1' AS r1,
       r2 || '/2' AS r2,
       r3 || '/3' AS r3,
       r4 || '/4' AS r4,
       r5 || '/5' AS r5,
       r6 || '/6' AS r6
FROM   (SELECT dept, rank FROM table_name)
PIVOT (
  COUNT(*)
  FOR rank IN (1 AS r1, 2 AS r2, 3 AS r3, 4 AS r4, 5 AS r5, 6 AS r6)
)

字符串
其中，对于样本数据：

CREATE TABLE table_name (dept, id, rank) AS
SELECT 'CSE',   1, 1 FROM DUAL UNION ALL
SELECT 'CSE',   2, 2 FROM DUAL UNION ALL
SELECT 'CSE',   3, 3 FROM DUAL UNION ALL
SELECT 'CSE',   4, 4 FROM DUAL UNION ALL
SELECT 'CSE',   5, 5 FROM DUAL UNION ALL
SELECT 'CSE',   6, 6 FROM DUAL UNION ALL
SELECT 'CSE',   7, 7 FROM DUAL UNION ALL
SELECT 'CSE',   8, 8 FROM DUAL UNION ALL
SELECT 'MECH',  9, 1 FROM DUAL UNION ALL
SELECT 'MECH', 10, 2 FROM DUAL UNION ALL
SELECT 'MECH', 11, 3 FROM DUAL UNION ALL
SELECT 'MECH', 12, 4 FROM DUAL UNION ALL
SELECT 'MECH', 13, 5 FROM DUAL UNION ALL
SELECT 'MECH', 14, 6 FROM DUAL UNION ALL
SELECT 'MECH', 15, 7 FROM DUAL UNION ALL
SELECT 'MECH', 16, 8 FROM DUAL UNION ALL
SELECT 'ELEC', 17, 6 FROM DUAL;

型
输出：
| R1| R2| R3| R4| R5| R6| R6 |
| --|--|--|--|--|--| ------------ |
| 1/1| 1/2| 1/3|四分之一|1/5| 1/6| 1/6 |
| 1/1| 1/2| 1/3|四分之一|1/5| 1/6| 1/6 |
| 0/1| 0/2| 0/3| 0/4| 0/5| 1/6| 1/6 |
fiddle

这里有一个选择;读取代码中的注解。
这是你的样本数据，稍微简化了一点：

SQL> with test (dept, id, rank) as
  2    (select 'CSE', 1, 1 from dual union all
  3     select 'CSE', 2, 2 from dual union all
  4     select 'CSE', 3, 3 from dual union all
  5     select 'CSE', 4, 4 from dual union all
  6    --
  7    select 'MECH',  9, 1 from dual union all
  8    select 'MECH', 10, 2 from dual union all
  9    select 'MECH', 11, 3 from dual union all
 10    select 'MECH', 12, 4 from dual union all
 11    --
 12    select 'ELEC', 17, 2 from dual
 13    ),

字符串
查询从这里开始。创建所有DEPT和RANK值的组合（稍后将在外部连接中使用）：

14  --
 15  all_depts as
 16    (select distinct dept from test),
 17  all_ranks as
 18    (select distinct rank from test),
 19  all_depts_ranks as
 20    (select d.dept, r.rank
 21     from all_depts d cross join all_ranks r
 22    )

型
最后，执行外部连接并使用条件聚合来产生所需的结果：

23  select a.dept,
 24    max(case when a.rank = 1 then nvl2(t.id, '1', '0') || '/' || a.rank end) r1,
 25    max(case when a.rank = 2 then nvl2(t.id, '1', '0') || '/' || a.rank end) r2,
 26    max(case when a.rank = 3 then nvl2(t.id, '1', '0') || '/' || a.rank end) r3,
 27    max(case when a.rank = 4 then nvl2(t.id, '1', '0') || '/' || a.rank end) r4
 28  from all_depts_ranks a left join test t on t.dept = a.dept and t.rank = a.rank
 29  group by a.dept
 30  order by a.dept;

DEPT R1    R2    R3    R4
---- ----- ----- ----- -----
CSE  1/1   1/2   1/3   1/4
ELEC 0/1   1/2   0/3   0/4
MECH 1/1   1/2   1/3   1/4

SQL>

型