你可以用 ROW_NUMBER() 分析函数，用于将这些状态值按字母顺序向下移动，以满足您的需要：

WITH T1 AS
(
SELECT DISTINCT (CASE
         WHEN (STATUS = 'Failed' ) THEN
           CASE WHEN PREREQUISITE_NM = 'Y' THEN 'Failed' 
                WHEN PREREQUISITE_NM = 'N' THEN 'Completed' 
           END 
         ELSE 
           STATUS 
         END) AS details
  FROM TABLE_LIST
 WHERE ID = 1
), T2 AS
(
 SELECT T1.*, ROW_NUMBER() OVER (ORDER BY details DESC) AS rn
   FROM T1
 )   
SELECT details
  FROM T2
 WHERE rn = 1

谢谢罗伯托和巴巴拉斯·欧尚，两个答案都很有用。我的一个同事分享了另一种方式。

WITH T1 AS (
SELECT
DISTINCT(DETAILS) 
FROM (
  SELECT STATUS, PREREQUISITE_NM, 
 (
CASE 
WHEN (STATUS = 'Failed' ) THEN
           CASE WHEN PREREQUISITE_NM = 'Y' THEN 'Failed' 
                WHEN PREREQUISITE_NM = 'N' THEN 'Completed' 
           END 
         ELSE 
           STATUS
           END
)DETAILS FROM TABLE_lIST WHERE ID=1)
),
T2 AS
(
SELECT CASE 
WHEN EXISTS
( SELECT DETAILS FROM  T1 where DETAILS='Pending' )
THEN 'Pending'  
WHEN EXISTS
( SELECT DETAILS FROM T1 WHERE DETAILS='InProgress' )
THEN 'InProgress'  
WHEN EXISTS
(SELECT DETAILS FROM T1 where DETAILS='Failed' )
THEN 'Failed'  
ELSE 'Completed' END  as DETAILS from DUAL 
                 )SELECT * FROM T2

在阅读了您的评论之后，我提出了这个解决方案，这也许不是最好的，但是在最后给出了您期望的输出，基于此，您实际想要的是将t1中生成的行转置为t2中的列
我使用了我自己的测试，请注意替换列或修改任何必要的内容以使其适应您自己的查询

SQL> desc my_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 VARCHAR2(20)
 C3                                                 VARCHAR2(1)

SQL> select * from my_test ;

        C1 C2                   C3
---------- -------------------- -
         1 Failed               Y
         2 Completed            Y
         1 Pending              Y
         1 Pending              N

SQL>

所以，如果我得到了第一部分的问题

SQL> WITH T1 AS (
     SELECT DISTINCT c2 FROM ( SELECT c1,c2,
     case  when c2='Completed'  then 'Completed'
           when c2='Pending'    then 'Pending'
           when c2='Failed' and c3 = 'Y' then 'Failed'
           when c2='Failed' and c3 = 'N' then 'Completed'
           else c2
           end FROM my_test WHERE c1=1
                 ) )
select trim ( completed || ' ' || failed || ' ' || pending ) as result
from
(
select * from t1 pivot ( max(c2) for c2 in ( 'Completed' as Completed, 'Failed' as Failed, 'Pending' as Pending ) )
)
/  9   10   11   12   13   14   15

RESULT
--------------------------------------------------------------
Failed Pending

现在我只需要在这个结果上构建一个案例（根据您自己的需求修改它）

SQL> WITH T1 AS (
            SELECT DISTINCT c2 FROM ( SELECT c1,c2,
                                                                                        case  when c2='Completed'  then 'Completed'
                                                  when c2='Pending'    then 'Pending'
                                                  when c2='Failed' and c3 = 'Y' then 'Failed'
                                                  when c2='Failed' and c3 = 'N' then 'Completed'
                                                                                        else c2
                                                                                        end FROM my_test WHERE c1=1
                 ) ) 
select 
case when result = 'Completed Failed Pending' then 'Pending' 
     when result = 'Completed Failed'         then 'Failed' 
     when result = 'Failed Pending'           then 'Failed' -- I guess
     when result = 'Completed Pending'        then 'Pending' -- I guess
end as output 
from (       
select trim ( completed || ' ' || failed || ' ' || pending ) as result 
from 
(                
select * from t1 pivot ( max(c2) for c2 in ( 'Completed' as Completed, 'Failed' as Failed, 'Pending' as Pending ) )
)
)
/

OUTPUT
-------
Failed

SQL>