Tricky view for Oracle

Question

I have a table "prices" with columns :

year, janprc, janqty, febprc, febqty ...

(prices and quantyties for all the months in a year)

what I need is to create a view "monthlyprices" with the columns :

year, month, price, quantity 

using data from the table above. how could i do that ?

Thanks!

Answer 1

Here is how to do it with one UNPIVOT statement and no UNIONs.

with t as (
  select 2008 year, 1 janprc, 500 janqty, 1 febprc, 600 febqty  from dual
  union
  select 2009,      50,       1000,       20,       3000        from dual
  union
  select 2010,      60,       1000,       25,       3000        from dual
)
SELECT *
FROM   t
UNPIVOT (
  (price, quantity) FOR month IN
  (
    (janprc, janqty) AS 'jan',
    (febprc, febqty) AS 'feb'
  )
)
order by
  year, month
;