Q

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

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 last published in February 2004

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close