Dynamic Query For Pivot In Clause

This is in continuation of this thread I have the below query to have the value as heading using SELECT * FROM (SELECT prod_id, start_date AS dt,

Solution 1:

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 ( ORDERBY dt )
  INTO   dates
  FROM   (
    SELECT start_date + LEVEL - 1AS 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 :1AND :2
   )
   PIVOT (
     SUM( tot_hours )
     FOR start_date IN (' || dates || ')
   )
   ORDERBY prod_id'USING start_date, end_date;
END;
/

PRINT cur;

Solution 2:

First of all, it's quite ridiculous to use pivot by dates when original date column also part of expected output. This introduces new columns but row count remains the same.

Secondly, it's not possible to make column names depend on bind values. Column names are defined on parse stage and Oracle re-uses the same plan for different binds.

If you, however, want to pass start and end_date as binds and have predefined column titles you can use either

• case (or decode) + group by
• pivot for xml

Update:

Normal solution

SQL>select prod_id, dt,
  2sum(decode(dt, :start_date, tot_hours)) start_date_hours,
  3sum(decode(dt, :end_date, tot_hours)) end_date_hours,
  4sum(tot_hours) tot_hours
  5from (select prod_id, start_date as dt, start_date, tot_hours
  6from prod_timings t)
  7groupby dt, prod_id
  8orderby dt desc, prod_id;

PROD_ID      DT            START_DATE_HOURS       END_DATE_HOURS  TOT_HOURS
------------ --------- -------------------- -------------------- ----------
PR220        19-SEP-17.00
PR2230       19-SEP-172.02
PR9036       19-SEP-17.6.6
PR9702       19-SEP-173.03
PR7127       18-SEP-17.00
PR9036       18-SEP-173.43.4
PR91034      18-SEP-174.04
PR9609       18-SEP-175.058rows selected.

Bizarre solution

SQL>with t as2  (select*3from (select prod_id, start_date as dt, start_date, tot_hours
  4from prod_timings t)
  5  pivot xml(sum(tot_hours) as s for start_date in6  (select :start_date from dual unionallselect :end_date from dual)))
  7select prod_id, dt, start_date_hours, end_date_hours,
  8         nvl(start_date_hours, end_date_hours) tot_hours
  9from t,
 10   xmltable('/PivotSet' passing start_date_xml
 11            columns
 12            start_date_hours number
 13            path '/PivotSet/item[1]/column[@name="S"]/text()',
 14            end_date_hours number
 15            path '/PivotSet/item[2]/column[@name="S"]/text()') x
 16orderby dt desc, prod_id;

PROD_ID      DT            START_DATE_HOURS       END_DATE_HOURS  TOT_HOURS
------------ --------- -------------------- -------------------- ----------
PR220        19-SEP-17.00
PR2230       19-SEP-172.02
PR9036       19-SEP-17.6.6
PR9702       19-SEP-173.03
PR7127       18-SEP-17.00
PR9036       18-SEP-173.43.4
PR91034      18-SEP-174.04
PR9609       18-SEP-175.058rows selected.

As for DSQL approach for this task... this is far away from common sense.