Q
Problem solve Get help with specific problems with your technologies, process and projects.

Creating a dynamic crosstab query with Oracle, part 2

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

Click to return to part 1.

CREATE OR REPLACE PACKAGE dynamic_cross_tab_query AS

    TYPE    ref_cursor IS REF CURSOR;
    PROCEDURE   return_result(p_query IN OUT ref_cursor, v_query OUT VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY dynamic_cross_tab_query IS
   
   FUNCTION in_quotes(v_value IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
          RETURN '''' ||v_value || ''''; 
   END in_quotes; 

    PROCEDURE return_result(p_query IN OUT ref_Cursor, v_query OUT VARCHAR2) IS

       attribute_select VARCHAR2(4000) := ' x.customer, x.srpid, '
       ||' COUNT(distinct c.pin) PIN_CNT, NVL
(d.carrier,'
                                          || in_quotes('Unknown') || ')
CARRIER, c.msgtype  ' ;

       return_query  LONG := 'SELECT ' || attribute_select ||  ', ' ;
       
  
   BEGIN

      /* 

I will recommend creating a synonym for remote table or for any table for that matter and ensure that a table name is not qualified by a schema... Code becomes portable that way. I am using it here to relate to your original query.

       */

       -- Columns to be retrieved
       --   x.Customer,  x.srpid, Count(c.pin), D.Carrier, c.msgtype, 
d.device_type 


       -- FROM Condition
        -- DEVICE_INFO   ==>  BBLG.DEVICE_INFO@PRLDW   (Alias is D)
        -- CDR_SUMMARY  ==>  BBLG.CDR_SUMMARY@PRLDW   (Alias is C)
        -- CURSOR_DETAILS ==> (CURSOR Retrieving S.id, s.sprid, f.customer by
        --                      JOINING BBLG.SRP_INFO@PRLDW (Alias is S)
               --                      and FINAL_CUSTOMER_LIST (Alias is f)) 
 (Alias is X)


       --  Where Condition

        --  c.dte between to_date('01-Dec-2003') AND to_date('01-Dec-2003')
        --  c.srpid_id = x.id
        --  c.pin = d.pin


       --  Grouping
       -- x.customer, x.srpid, d.carrier,c.msgtype, d.device_type


       ---    Display Requirement

       --Customer SRPID  CARRIER  MESSAGE_TYPE Device_1 ..  Device n ....
         

      /* If Device_TYPE is unique in Device_info you do not need distinct */

      FOR cur_rec IN
         
       (SELECT DISTINCT NVL(DEVICE_TYPE,'Unknown')  device_type
                   FROM DEVICE_INFO)  LOOP

  return_query := return_query ||
                     'MAX( DECODE(device_type,' || in_quotes
(cur_rec.device_type) || ',' || 
                                                   in_quotes('X') ||
                     ' , NULL) ) ' || cur_rec.device_type || ' ,';


     END LOOP; 

     return_query := RTRIM(return_query,',')
                    || ' FROM device_info D, cdr_summary C, cursor_details x '
                    || ' WHERE c.dte between to_date(' || in_quotes('01-Dec-2003')
 || ') AND to_date('
                    || in_quotes('01-Dec-2003') || ' ) AND c.srpid = x.id'
                    || ' AND c.pin = d.pin '
                    || '  GROUP BY x.customer, x.srpid, d.carrier,c.msgtype,
 d.device_type ';

    v_query := return_query;

    OPEN p_query for return_query;

         
  END return_result;
END dynamic_cross_tab_query;
/

Dig Deeper on Using Oracle PL-SQL

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close