Ask the Expert

Creating a dynamic crosstab query with Oracle, part 1

I would like to know if it is possible to create a dynamic crosstab query with Oracle. If yes, how would I go about creating one? Here is my SQL. I would like the crosstab query to display DEVICE_TYPEs as column names -- device type will have to be dynamic. DECODE statement is not an option, since the device type is always changing. 
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

    Requires Free Membership to View

    Login

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.

Click for the code in part 2.

This was first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top