CODEWITHSUNDEEP
sqloracle-database
user1009073
user1009073

Reputation: 3238

Oracle SQL - How to "pivot" one row to many

In Oracle 12c, I have a view, which takes a little time to run. When I add the where clause, it will return exactly one row of interest. The row has columns/value like this...

enter image description here

I need this flipped so that I can see one row per EACH "set". I need the SQL to return something like enter image description here

I know I can do a UNION ALL for each of the entry sets, but as the view takes a little while to run, plus there are about 30 different sets (I only showed 3 - Car, Boat, and truck)

enter image description here

Is there a better way of doing this? I have looked at PIVOT/UNPIVOT, but I didn't see how to make this work.

Upvotes: 0

Views: 581

Answers (1)

msheikh25
msheikh25

Reputation: 578

I think you are looking for UNPIVOT

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'
    )
  )
;

OUTPUT:

TYPE       PRICE        TAX
----- ---------- ----------
CAR            1          2 
BOAT           3          4 
TRUCK          5          6

Upvotes: 1

Related Questions