本文介绍了在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中将数据透视表转换为平面表
发布评论