0

Consider the following table:

enter image description here

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

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)
user272735
  • 10,473
  • 9
  • 65
  • 96
Mayou
  • 8,498
  • 16
  • 59
  • 98
  • possible duplicate of [What is the equivalent of SQL Server APPLY in Oracle?](http://stackoverflow.com/questions/1476191/what-is-the-equivalent-of-sql-server-apply-in-oracle) – Pablo Sep 25 '13 at 16:01
  • 1
    I don't see it as a duplicate question - this is a specific case whereas the original question was very generic and the answer does not apply at all in this instance – Andrew not the Saint Sep 25 '13 at 16:09
  • Not a duplicate at all. Here I am looking for the use of `apply` in the context of a pivot table. – Mayou Sep 25 '13 at 16:09

1 Answers1

1

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
Andrew not the Saint
  • 2,496
  • 2
  • 17
  • 22