Dynamic Oracle Pivot_In_Clause

Dynamic Oracle Pivot_In_Clause

You can build dynamic query in your script,
look at this example:

variable rr refcursor

declare 
  bb varchar2(4000);
  cc varchar2( 30000 );
begin 
    WITH PIVOT_DATA AS (
         SELECT *
         FROM
         (
             SELECT USERNAME, GRANTED_ROLE
             FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R
             ON U.USERNAME = R.GRANTEE
          )
    )
    select ''''|| listagg( granted_role, ''',''' ) 
            within group( order by granted_role ) || '''' as x 
    into bb
    from (
      select distinct granted_role from pivot_data
    )
    ;

    cc := q'[
    WITH PIVOT_DATA AS (
         SELECT *
         FROM
         (
             SELECT USERNAME, GRANTED_ROLE
             FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R
             ON U.USERNAME = R.GRANTEE
          )
    )
    SELECT *
    FROM PIVOT_DATA
    PIVOT
    (
        COUNT(GRANTED_ROLE)
        FOR GRANTED_ROLE
        IN(]'  || bb || q'[) -- Just an example
    )
    ORDER BY USERNAME ASC]';

    open :rr for cc;
end;
/

SET PAGESIZE 200
SET LINESIZE 16000
print :rr

Here is the result (only small fragment, because it is very wide and long)

-----------------------------------------------------------------------------------------------------------------------------------
    USERNAME                       'ADM_PARALLEL_EXECUTE_TASK' 'APEX_ADMINISTRATOR_ROLE' 'AQ_ADMINISTRATOR_ROLE' 'AQ_USER_ROLE'        
    ------------------------------ --------------------------- ------------------------- ----------------------- ----------------------
    ANONYMOUS                      0                           0                         0                       0          
    APEX_030200                    0                           0                         0                       0        
    APEX_PUBLIC_USER               0                           0                         0                       0    
    APPQOSSYS                      0                           0                         0                       0   
..............
    IX                             0                           0                         1                       1  
    OWBSYS                         0                           0                         1                       1      

SQL Pivot with dynamic values for IN CLAUSE

An option which uses a substitution variable:

SQL> set verify off
SQL> clear columns
columns cleared
SQL> column llist new_value slist
SQL> select listagg(ym, ', ') within group (order by ym) as llist
  2  from (select distinct
  3          chr(39) || year_month ||chr(39) || ' as "' || year_month ||'"' ym
  4        from details
  5      );

LLIST
--------------------------------------------------------------------------------
'2020-05' as "2020-05", '2020-06' as "2020-06"

SQL> select * from
  2  (select lob, clevel, classification, year_month, ccount from details)
  3     pivot (sum(ccount)
  4            for year_month in (&slist));

LOB               CLEVEL CLASSIFIC    2020-05    2020-06
----------------- ------ --------- ---------- ----------
Finance           level3 statement                    65
Wealth Management level2 lending           23
Mergers and Acqu  level3 statement        356

SQL>

Its drawback is that it'll fail if LISTAGG's result is longer than 4000 characters (but, then again, what will you do with a result that contains more than 500 months?).

Dynamic Pivot in Oracle's SQL

You can't put a non constant string in the IN clause of the pivot clause.

You can use Pivot XML for that.

From documentation:

subquery A subquery is used only in conjunction with the XML keyword.
When you specify a subquery, all values found by the subquery are used
for pivoting

It should look like this:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;

You can also have a subquery instead of the ANY keyword:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;

Here is a sqlfiddle demo

Dynamic Query for PIVOT In Clause

Something like:

VARIABLE cur REFCURSOR;

DECLARE
  dates VARCHAR2(4000);
  start_date DATE := DATE '2017-09-18';
  end_date   DATE := DATE '2017-09-19';
BEGIN
  SELECT LISTAGG(
           'DATE ''' || TO_CHAR( dt, 'YYYY-MM-DD' )
           || ''' AS "' || TO_CHAR( dt, 'FMDY-DD' ) || '"',
           ','
         ) WITHIN GROUP ( ORDER BY dt )
  INTO   dates
  FROM   (
    SELECT start_date + LEVEL - 1 AS dt
    FROM   DUAL
    CONNECT BY LEVEL <= end_date - start_date + 1
  );

  OPEN :cur FOR
  'SELECT * FROM (
     SELECT t.*,
            MIN( start_date ) OVER ( PARTITION BY prod_id ) AS min_start_date,
            SUM( tot_hours  ) OVER ( PARTITION BY prod_id ) AS prod_tot_hours
     FROM   prod_timings t
     WHERE  start_date BETWEEN :1 AND :2
   )
   PIVOT (
     SUM( tot_hours )
     FOR start_date IN (' || dates || ')
   )
   ORDER BY prod_id'
   USING start_date, end_date;
END;
/

PRINT cur;

Pivot with Dynamic Columns in Oracle

SYS_REFCURSOR within a stored function(eg. PL/SQL used instead of using SQL directly) might be used in order to get dynamically generated result set(eg. Dynamic Pivot). In this case, a string for Conditional Aggregation is generated :

CREATE OR REPLACE FUNCTION get_passengers_rs RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_str       VARCHAR2(32767);
BEGIN
  SELECT LISTAGG('MAX(CASE WHEN rn = '||lvl||' THEN age||''(''||passengers||'')'' END) 
                   AS "Age'||lvl||'"' ,',') WITHIN GROUP (ORDER BY 0)
    INTO v_str
    FROM ( SELECT level AS lvl  
             FROM dual
          CONNECT BY level <= (SELECT MAX(COUNT(*)) FROM t GROUP BY ID ) ) t;  

  v_sql :=
  'SELECT ID, '|| v_str ||'
     FROM 
     (
      SELECT t.*, 
             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
        FROM t     
      )
    GROUP BY ID';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

I also added the names of passengers in order to distinguish each data well.

Then run the below code :

VAR rc REFCURSOR
EXEC :rc := get_passengers_rs;
PRINT rc

from SQL Developer's Command Line in order to see the expected result set.

Above code generates this SQL string(v_sql) for the currently existing data

SELECT ID, MAX(CASE WHEN rn = 1 THEN age||'('||passengers||')' END) AS "Age1",
           MAX(CASE WHEN rn = 2 THEN age||'('||passengers||')' END) AS "Age2",
           MAX(CASE WHEN rn = 3 THEN age||'('||passengers||')' END) AS "Age3"
  FROM 
     (
      SELECT t.*, 
             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
        FROM t     
      )
 GROUP BY ID

which yields

ID      Age1        Age2            Age3
123456  58(Marie)   65(Ben) 
123458  32(Aaron)   18(Caroline)    37(Stephanie)

as result set.

---

Related Topics