oracle : Dynamic column name [duplicate]

Question

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 if this gets solved, my real problem gets solved. Here are my tables :

tbl_products

    tp_id | tp_name 

     1      apple

     2      mango

     3      pineapple

tbl_sales

    ts_id | ts_location | ts_tp_id | ts_sales

       1        NY              2           5
       2        LN              2           10  
       3        QL              1           25          
       4        QL              3           20
       5        LN              3           35
       6        NY              3           50
       7        NY              1           100

If I have these two tables, tbl_products and tbl_sales, how to create a query that has a dynamic columns like this :

 sales_location | apple | mango | pineapple 
      NY           100       5        50

where apple, mango and pineapple should have the total number of sales according to location

Answer 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(case when p.tp_name = 'apple' then s.ts_sales else 0 end) Apple,
  sum(case when p.tp_name = 'mango' then s.ts_sales else 0 end) Mango,
  sum(case when p.tp_name = 'pineapple' then s.ts_sales else 0 end) Pineapple
from tbl_sales s
inner join tbl_products p
  on s.ts_tp_id = p.tp_id
group by 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 (select distinct tp_name from tbl_products order by 1)
        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);
        end loop;

        sql_query := sql_query || ' from tbl_sales s 
                                                inner join tbl_products p
                                                  on s.ts_tp_id = p.tp_id
                                                group by 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 |