Oracle : Dynamic Column Name

Possible Duplicate: Oracle Rows to Column Transformation My real problem is just like this but has many tables related to each other. So, I just created a example, so assuming i

Solution 1:

Since you are using Oracle10g, there is no PIVOT function so you will have to perform this type of transformation using an aggregate function with a CASE statement.

If the values are known ahead of time, then you can hard code them in a static version:

select s.ts_location,
  sum(casewhen p.tp_name ='apple'then s.ts_sales else0end) Apple,
  sum(casewhen p.tp_name ='mango'then s.ts_sales else0end) Mango,
  sum(casewhen p.tp_name ='pineapple'then s.ts_sales else0end) Pineapple
from tbl_sales s
innerjoin tbl_products p
  on s.ts_tp_id = p.tp_id
groupby s.ts_location

See SQL Fiddle with Demo

But if you values are not known ahead of time, then you have to implement dynamic sql and in Oracle you will want to use a procedure for this:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select s.ts_location ';

    begin
        for x in (selectdistinct tp_name from tbl_products orderby1)
        loop
            sql_query := sql_query ||
                ' , sum(case when p.tp_name = '''||x.tp_name||''' then s.ts_sales end) as '||x.tp_name;

                dbms_output.put_line(sql_query);
        endloop;

        sql_query := sql_query || ' from tbl_sales s 
                                                inner join tbl_products p
                                                  on s.ts_tp_id = p.tp_id
                                                groupby s.ts_location';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then to return the results you can use (note: this is how I do it in Toad):

variable x refcursor
exec dynamic_pivot(:x)
print x

Both will return the result:

| TS_LOCATION | APPLE | MANGO | PINEAPPLE |
-------------------------------------------
|          LN |     0 |    10 |        35 |
|          QL |    25 |     0 |        20 |
|          NY |   100 |     5 |        50 |