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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close