Creating a dynamic crosstab query with Oracle, part 1
SELECT /*+ Index (C cdr_sum_dte) */ x.customer, x.srpid, COUNT(distinct c.pin) PIN_CNT, NVL(d.carrier,'Unknown') CARRIER, c.msgtype, NVL(d.device_type,'Unknown') DEVICE_TYPE FROM [email protected] C, [email protected] D, (SELECT /*+ Index (S sio_uk) */ s.id, s.srpid, f.customer FROM [email protected] s, FINAL_CUSTOMER_LIST f WHERE s.srpid=f.srp_id AND f.c_source in ('SHIP_TO', 'T_SUPPORT') )x WHERE c.dte between to_date('01-Dec-2003') AND to_date('01-Dec-2003') AND c.srpid_id = x.id AND c.pin = d.pin GROUP BY x.customer, x.srpid, d.carrier,c.msgtype, d.device_type
In order to achieve your objective you cannot strictly use SQL. You have to use REF CURSOR to return your result through a PL/SQL package. Below, I have provided a possible solution for your query.
The query output will be as follows:
Outcome #1
| CUSTOMER | SRPID | PIN_CNT | CARRIER | MSGTYPE | Device 1 | ... | Device n |
| - | - | - | - | - | - | - | - |
However, I do not know whether you want to have the result as follows
Outcome #2
| Attribute | Device 1 | ... | Device n |
| Customer | CUSTOMER1 | ... | Customer n |
| SRPID | SRPTID 1 | ... | SRPID n |
| PIN_CNT | PIN_CNT 1 | ... | PIN_CNT n |
| CARRIER | CARRIER 1 | ... | CARRIER n |
| MSGTYPE | MSGTYPE 1 | ... | MSG_TYPE n |
The solution provided on the next page is for the first one. However, if your desired output is the latter, and while I am working on a solution similar to the one one the next page for the second requirement, you can try another solution: You may modify the program to dynamically create a temporary table with one column called "Attribute." Then for each entry in DEVICE_INFO table you can alter the temporary table to add the column named as the DEVICE_TYPE value in DEVICE_INFO table. Now, using your original SQL as an IN REF_CURSOR parameter you can use PL/SQL logic to populate the temporary table accordingly. Now, you can use a ref_cursor variable and open it as SELECT * from the temporary table. The desired outcome will be similar to that of Outcome #2 above.
