unpivot (or cross apply) multiple columns

0

Please login or register to vote for this query.

(click on this box to dismiss)

Esperanto Language

Q&A for teachers and students of the Esperanto language

/*
http://stackoverflow.com/questions/37033156/oracle-sql-how-to-pivot-one-row-to-many/37035231#37035231
*/

WITH TEMP_DATA (ID1, CarPrice, CarTax, BoatPrice, BoatTax, TruckPrice, TruckTax)
AS (select 'AAA', 1, 2, 3, 4, 5, 6)

  select TYPE, PRICE, TAX 
  from temp_data td
  CROSS APPLY
  (
    VALUES
      ('CAR',CarPrice, CarTax) ,
      ('BOAT',BoatPrice, BoatTax),
      ('TRUCK',TruckPrice, TruckTax)
  ) td1 (TYPE, PRICE, TAX)
;

/* ORACLE SOLUTION:
WITH TEMP_DATA (ID1, CarPrice, CarTax, BoatPrice, BoatTax, TruckPrice, TruckTax)
AS (
select 'AAA', 1, 2, 3, 4, 5, 6 from dual )

  select TYPE, PRICE, TAX 
  from temp_data
  unpivot
  (
    (PRICE, TAX) 
    for TYPE IN 
    (
      (CarPrice, CarTax) as 'CAR',
      (BoatPrice, BoatTax) as 'BOAT',
      (TruckPrice, TruckTax) as 'TRUCK'
    )
  )
;
*/

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results