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...
I need this flipped so that I can see one row per EACH "set". I need the SQL to return something like
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)
Is there a better way of doing this? I have looked at PIVOT/UNPIVOT, but I didn't see how to make this work.
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' ) ) ;
TYPE PRICE TAX ----- ---------- ---------- CAR 1 2 BOAT 3 4 TRUCK 5 6