oracle : Dynamic column name

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

@bluefeet Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod — hsuk, Jan 04 '13 at 12:18
I don't know why has the moderators closed this question. I don't think the question is possible duplicate for the one you guys have mentioned. Its totally different in sense, I need dynamic columns. (the no. of columns is not fixed). On other question, you guys can see the solution is made on the way, the columns are always fixed. The solution has manual entry of the columns. — hsuk, Jan 07 '13 at 06:26

score 3 · Accepted Answer · answered Jan 04 '13 at 12:22

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 |

is it possible without creating stored procedure ? Is there some way to print the generated dynamic query ? — hsuk, Jan 07 '13 at 06:03
if the cursor is required, what value should I provide for :x ? — hsuk, Jan 07 '13 at 07:02

oracle : Dynamic column name

1 Answers1

Linked