Oracle: dynamic column name

Question

Possible duplicate:
Oracle Column Conversion Rows

My real problem is the same, but has many tables related to each other. So, I just created an example, therefore, assuming that if this is solved, my real problem will be 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 with dynamic columns like this:

  sales_location | apple | mango | pineapple NY 100 5 50 

where the apple, mango and pineapple must have a total number of sales depending on location

Answer 1

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

If the values ​​are known in advance, you can copy them in the 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 do not know the value in advance, then you need to implement dynamic sql and in Oracle you will want to use the 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 return the result:

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