在Oracle SQL中将数据透视表转换为平面表 [英] Converting a pivot table to flat table in Oracle SQL

问题描述

请考虑下表:

我正在使用以下查询(在 SQL Server 中)将此表转换为平面表，如下所示:

I am using the query below (in SQL Server) to convert this table to a flat table as follows:

我也想使用Oracle SQL.但是，该查询不适用于"Oracle SQL"语言:以下使用的cross apply在Oracle中不起作用.知道如何使用Oracle SQL等效地编写此代码吗?谢谢！

I would like to the same thing using Oracle SQL. However the query does not work in "Oracle SQL" language: cross apply, which is used below, does not work in Oracle. Any idea how to write this equivalently using Oracle SQL? Thanks!

select t.employee_id,
   t.employee_name,
   c.data,
   c.old,
   c.new
 from test_table t
 cross apply
 (
   select 'Address', Address_Old, Address_new union all
   select 'Income', cast(income_old as varchar(15)), cast(income_new as varchar(15))
 ) c (data, old, new)

推荐答案

如果没有执行交叉应用的功能，则不那么简洁:

Not quite as succinct without the ability to do CROSS APPLY:

select t.employee_id,
t.employee_name,
c.data,
DECODE (c.data, 'Address', t.address_old, 'Income', t.income_old) AS old,
DECODE (c.data, 'Address', t.address_new, 'Income', t.income_new) AS new
from test_table t
cross join
(
  select 'Address' AS data
  from dual
  union all
  select 'Income'
  from dual
 ) c

这篇关于在Oracle SQL中将数据透视表转换为平面表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！