如果列的数量是固定的（看起来是），你可以使用 CASE 根据相关表格选择具体数值。那么聚合就很简单了。
例如：

select
  batch_number,
  max(defect_of_interest) as defect_of_interest,
  max(defect_count) as maximum_defect_count
from (
  select
    d.batch_number,
    b.defect_of_interest,
    case when b.defect_of_interest = 'SCRATCHES' then d.scratches
        when b.defect_of_interest = 'BUMPS' then d.bumps
        when b.defect_of_interest = 'STAINS' then d.stains
    end as defect_count
  from defect_details d
  join batches b on b.batch_number = d.batch_number
) x
group by batch_number
order by batch_number;

参见db<>fiddle中的oracle示例。