如何将2 cols 1行移动到oracle中的1 cols 2行

Question

我有一个疑问，我似乎搞错了。我想改变结果集，所以它是1色，可以是N行。

原始查询

select src_approval, dst_approval
from example_table
where id = 62615
group by src_approval, dst_approval

我尝试了以下不正确的方法。

select src_approval, dst_approval
from example_table
unpivot
( colvalue for col in (src_approval, dst_approval) )
where id = 62615
group by src_approval, dst_approval

我尝试了下面的方法，它仍然给了我2列

select *
from   ( 
select src_approval, dst_approval
from example_table
where id= 62615
group by src_approval, dst_approval
)
 unpivot
       ( income_component_value
         for income_component_type in (src_approval, dst_approval)
       )

结果

select *
from   ( 
select src_approval, dst_approval
from e_p
where exemption_id = 62615
group by src_approval, dst_approval
)
 unpivot
       ( owner
         for approval in (src_approval, dst_approval)
       )

Answer 1

如果考虑使用unpivot，可以使用横向连接：

select distinct x.dte
from example_table et cross join lateral
     (select et.src_approval as dte from dual union all
      select et.dst_approval as dte from dual
     ) x
where id = 62615;

更常见的是，这只是使用union编写的。

select et.src_approval
from example_table et
union     -- on purpose to remove duplicates
select et.dst_approval
from example_table et;