我建议稍微改变一下方法：
1.对于每个订单，聚合所有状态，按行的创建日期排序
1.在列表中查找缺少的状态

SELECT count(
       CASE
         WHEN INSTR(status_array, 'Approved') > 0 THEN 0
         ELSE 1) as 'Not approved', 
       ... Repeat for each status
FROM (
  SELECT order_id, LISTAGG(status, ',') WITHIN GROUP (ORDER BY created_at) AS status_array
  FROM Orders
  GROUP BY order_id
) t;

字符串
请注意，这只是一般的想法，我没有你的数据，所以我不能为你执行查询。

您需要包含GROUP BY子句，并可以使用条件聚合：

SELECT order#,
       MAX(CASE status WHEN 'Fulfilled' THEN 1 ELSE 0 END) AS is_fulfilled,
       MAX(CASE status WHEN 'Dispatched' THEN 1 ELSE 0 END) AS is_dispatched,
       MAX(CASE status WHEN 'Approved' THEN 1 ELSE 0 END) AS is_approved,
       MAX(CASE status WHEN 'Cancelled' THEN 1 ELSE 0 END) AS is_cancelled,
       MAX(CASE status WHEN 'Initiated' THEN 1 ELSE 0 END) AS is_initiated
FROM   orders
GROUP BY order#

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

CREATE TABLE orders (order#, status) AS
SELECT 'A123', 'Fulfilled' FROM DUAL UNION ALL
SELECT 'A123', 'Dispatched' FROM DUAL UNION ALL
SELECT 'A123', 'Approved' FROM DUAL UNION ALL
SELECT 'A123', 'Initiated' FROM DUAL UNION ALL
SELECT 'A456', 'Cancelled' FROM DUAL UNION ALL
SELECT 'A456', 'Initiated' FROM DUAL UNION ALL
SELECT 'B123', 'Fulfilled' FROM DUAL UNION ALL
SELECT 'B123', 'Dispatched' FROM DUAL UNION ALL
SELECT 'B123', 'Initiated' FROM DUAL UNION ALL
SELECT 'B456', 'Fulfilled' FROM DUAL UNION ALL
SELECT 'B456', 'Approved' FROM DUAL UNION ALL
SELECT 'B456', 'Initiated' FROM DUAL;

型
输出：
| 已完成|已发货|已批准|IS_取消|已启动| IS_INITIATED |
| --|--|--|--|--| ------------ |
| 一个|一个|一个|0个|一个| 1 |
| 0个|0个|0个|一个|一个| 1 |
| 一个|一个|0个|0个|一个| 1 |
| 一个|0个|一个|0个|一个| 1 |
fiddle