又一个变化。
样本数据：

SQL> WITH
  2     aux (elemento, valor)
  3     AS
  4        (SELECT 'ELEMENT1_SUFFIX1', 2 FROM DUAL
  5         UNION ALL
  6         SELECT 'ELEMENT1_SUFFIX2', 4 FROM DUAL
  7         UNION ALL
  8         SELECT 'ELEMENT2_SUFFIX1', 5 FROM DUAL
  9         UNION ALL
 10         SELECT 'ELEMENT2_SUFFIX2', 15 FROM DUAL
 11        )

质询：

12  -- existing rows
 13  select elemento, valor
 14    from aux
 15  union all
 16  -- calculated rows
 17  select
 18    substr(elemento, 1, instr(elemento, '_') - 1) ||'_RESULT' as elemento,
 19    max(case when substr(elemento, -1) = 2 then valor end) /
 20    max(case when substr(elemento, -1) = 1 then valor end) as valor
 21  from aux
 22  group by substr(elemento, 1, instr(elemento, '_') - 1);

ELEMENTO                     VALOR
----------------------- ----------
ELEMENT1_SUFFIX1                 2
ELEMENT1_SUFFIX2                 4
ELEMENT2_SUFFIX1                 5
ELEMENT2_SUFFIX2                15
ELEMENT1_RESULT                  2
ELEMENT2_RESULT                  3

6 rows selected.

SQL>

我们可以在这里使用聚合/透视方法沿着联合：

WITH cte AS (
    SELECT SUBSTR(ELEMENTO, 1, INSTR(ELEMENTO, '_') - 1) || '_RESULT' AS ELEMENTO,
           MAX(CASE WHEN SUBSTR(ELEMENTO, INSTR(ELEMENTO, '_') + 1) = 'SUFFIX2'
                    THEN VALOR END) /
           MAX(CASE WHEN SUBSTR(ELEMENTO, INSTR(ELEMENTO, '_') + 1) = 'SUFFIX1'
                    THEN VALOR END) AS VALOR
    FROM yourTable
    GROUP BY SUBSTR(ELEMENTO, 1, INSTR(ELEMENTO, '_') - 1)
)

SELECT ELEMENTO, VALOR
FROM
(
    SELECT ELEMENTO, VALOR, 1 AS pos FROM yourTable
    UNION ALL
    SELECT ELEMENTO, VALOR, 2 FROM cte
) t
ORDER BY pos, ELEMENTO;

试试这个：

with aux (elemento, valor) as
  ( select 'ELEMENT1_SUFFIX1', 2 from dual
  
  UNION ALL
  
  select 'ELEMENT1_SUFFIX2', 4 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX1', 5 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX2', 15 from dual
  ), rawdata (elemento, valor, group_id, row_id) AS
  (
select aux.*
      ,DENSE_RANK() OVER (ORDER BY SUBSTR(elemento, 1, INSTR(elemento, '_')-1))
      ,ROW_NUMBER() OVER (PARTITION BY SUBSTR(elemento, 1, INSTR(elemento, '_')-1) ORDER BY SUBSTR(elemento, INSTR(elemento, '_')+1))
from aux
    )
    SELECT *
    FROM aux
    
    UNION ALL
    
    SELECT CONCAT(CONCAT('ELEMENT', A.group_id),'_RESULT')
          ,B.valor / a.valor
    FROM rawdata A
    INNER JOIN rawdata B
       ON A.group_id = b.group_id
       AND A.row_id = b.row_id -1