Converting columns to rows in oracle 11gR1

Question

I saw there many example on this site but still i havent got any solution.So iam posting the question.
Please suggest me how can i resolve this issue. Iam working on oracle 11gR1 version.

year         price     Quantity
1991           10        50
2008           20        96

I want output as

1991  10
1991  20
2008  50
2008  96

I tried with pivot function but not achieved and getting exception as SQL command not terminated properly.
below is my query. Iam not good in sql .

select * from   (select year, price ,quanty from my_table )
       pivot( min(year)  year  in (price, quanty) );

Edit for above question:

select year, value
from my_table
unpivot
(
  value
  for col in (price, quantity)
) u

For the above query, if i have one more column by name Name of Product which is varchar, iam getting and i pass the column in above query as below .

select year, value
    from my_table
    unpivot
    (
      value
      for col in (price, quantity,productname)
    ) u

getting error as

ORA-01790: expression must have same datatype as corresponding expression

Please @BlueFeet suggest on this.

Answer 1

It appears that you need to UNPIVOT instead of pivot. The unpivot is the process of converting multiple rows into multiple columns.

Since you are using Oracle 11g, you can use the unpivot function:

select year, value
from my_table
unpivot
(
  value
  for col in (price, quantity)
) u

See SQL Fiddle with Demo.

You could also write this using UNION ALL:

select year, price as value
from my_table
union all
select year, quantity as value
from my_table

See SQL Fiddle with Demo

Based on the fact that you also want to include a varchar column in the final result, you'll need to convert the columns to all be the same datatype - you can do this in a subquery:

select year, value
from
(
  select year, 
    to_char(price) as price,
    to_char(quantity) as quantity,
    productname
  from my_table
)
unpivot
(
  value
  for col in (price, quantity, productname)
) u;

See SQL Fiddle with Demo

Answer 2

Try this:

with t(year, price, Quantity) as(
  select 1991, 10, 50 from dual union all
  select 2008, 20, 96 from dual 
)
select year, new_col1
  from t
unpivot (
  new_col1 for new_col in (price, quantity))

YEAR    NEW_COL1
----------------
1991    10
1991    50
2008    20
2008    96

Read more here