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 BBLG.CDR_SUMMARY@PRLDW C, BBLG.DEVICE_INFO@PRLDW D,
         (SELECT /*+ Index (S sio_uk) */ s.id, s.srpid, f.customer
              FROM BBLG.SRP_INFO@PRLDW 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

The query output will be as follows:

Outcome #1

However, I do not know whether you want to have the result as follows

Outcome #2

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.

Click for the code in part 2.