可以以cross join语句开始，以case..when语句结束，如下所示：

with tab( cat1 , priceA , priceB , type1 , 
          cat2 , costA  , costB  , type2 ) as
(
 select 'UK' , 55, 70 , 'X' , 'CAN' , 25 , 15 , 'Z' from dual union all    
 select 'UK' , 30, 26 , 'Y' , 'IND' , 20 , 20 , 'Z' from dual union all    
 select 'NZ' , 38, 36 , 'Z' , 'GER' , 40 , 25 , 'Y' from dual union all    
 select 'USA', 47, 49 , 'Z' , 'AUS' , 60 , 15 , 'X' from dual
), tab2 as
(
select t1.cat1 , t1.priceA , t1.priceB , t1.type1,
       t2.cat2 , t2.costA  , t2.costB  , t2.type2, 
       row_number() over ( partition by t1.cat1, t1.type1 order by t1.cat1, t1.type1 ) rn
  from tab t1
  cross join tab t2
), tab3 as
(
select t.*, mod(rn,2) as r2
  from tab2 t
) 
select distinct
       ( case when r2 = 1 then cat1 else cat2 end ) as cat1,
       ( case when r2 = 1 then priceA else costA end ) as priceA,
       ( case when r2 = 1 then priceB else costB end ) as priceB,
       ( case when r2 = 1 then type1 else type2 end ) as type1
  from tab3;

CAT1 PRICEA PRICEB  TYPE1
AUS  60     15      X
UK   55     70      X
NZ   38     36      Z
UK   30     26      Y
USA  47     49      Z
IND  20     20      Z
CAN  25     15      Z
GER  40     25      Y

Demo

取消透视的有效方法是：

select (case when n.n = 1 then cat1 else cat2 end) as cat,
       (case when n.n = 1 then priceA else costA end) as totalB,
       (case when n.n = 1 then priceB else costB end) as totalB,
       (case when n.n = 1 then type1 else typ2 end) as type
from t cross join
     (select 1 as n from dual union all select 2 from dual) n;

我不知道这是否违反了union all的奇怪限制，但这也可以表述为：

from t cross join
     (select rownum as n from t where rownum <= 2) n;

在Oracle 12C中，横向连接是可行的方法（尽管这仍然需要union all：

select v.*
from t cross apply
     (select t.cat1 as cat3, t.priceA as totalA, t.priceB as totalB, t.type1 as type union all
      select t.cat2, t.costA, t.costB, t.type2
     ) v;

最后，union all可以满足CTE：

with cte as (
      . . .
     )
select t.cat1 as cat3, t.priceA as totalA, t.priceB as totalB, t.type1 as type
from cte t
union all
select t.cat2, t.costA, t.costB, t.type2
from cte t;

如果优化器认为CTE是最佳方法，Oracle将实现CTE，从而消除性能问题。