Dynamic Query for PIVOT In Clause

Question

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,
                 start_date,
                 hours
            FROM prod_timings t) PIVOT (SUM (hours)
                                 FOR start_date
                                 IN  (TO_DATE ('18-SEP-17', 'DD-MON-YY') AS wed,
                                     TO_DATE ('19-SEP-17', 'DD-MON-YY') AS thu))
ORDER BY prod_id, dt

Can I use the below query to use inside the IN clause of PIVOT to have the dates dynamic? The idea is to have a dynamic query inside the IN clause to avoid hard-coding of dates

SELECT *
  FROM (    SELECT (TO_DATE (:end_date, 'DD-MM-YYYY') - LEVEL + 1) AS day
              FROM DUAL
        CONNECT BY LEVEL <=
                      (  TO_DATE (:end_date, 'DD-MM-YYYY')
                       - TO_DATE (:start_date, 'DD-MM-YYYY')
                       + 1))

Bind values

end_date - 19-Sep-17
start_date - 18-Sep-17

The output of the above is

19-Sep-17
18-Sep-17

Expected output is

╔═════════╦════════════╦════════╦════════╦═══════════╗
║ PROD_ID ║ START_DATE ║ MON-18 ║ TUE-19 ║ TOT_HOURS ║
╠═════════╬════════════╬════════╬════════╬═══════════╣
║ PR220   ║ 19-Sep-17  ║        ║ 0      ║ 0         ║
║ PR2230  ║ 19-Sep-17  ║        ║ 2      ║ 2         ║
║ PR9702  ║ 19-Sep-17  ║        ║ 3      ║ 3         ║
║ PR9036  ║ 19-Sep-17  ║        ║ 0.6    ║ 0.6       ║
║ PR9036  ║ 18-Sep-17  ║ 3.4    ║        ║ 3.4       ║
║ PR9609  ║ 18-Sep-17  ║ 5      ║        ║ 5         ║
║ PR91034 ║ 18-Sep-17  ║ 4      ║        ║ 4         ║
║ PR7127  ║ 18-Sep-17  ║ 0      ║        ║ 0         ║
╚═════════╩════════════╩════════╩════════╩═══════════╝

Table structure and values

CREATE TABLE PROD_TIMINGS
(
  PROD_ID     VARCHAR2(12 BYTE),
  START_DATE  DATE,
  TOT_HOURS   NUMBER
);

SET DEFINE OFF;
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR220', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR2230', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9702', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9036', TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0.6);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9036', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3.4);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR9609', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR91034', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into PROD_TIMINGS
   (PROD_ID, START_DATE, TOT_HOURS)
 Values
   ('PR7127', TO_DATE('09/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0);
COMMIT;

MT0 · Accepted Answer · 2017-09-27T10:41:02.887

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 ( 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;

edited Sep 27 '17 at 10:41

answered Sep 27 '17 at 10:34

MT0

143,790
11
59
117

Now it makes sense to explain what the point of using DSQL if result can be achieved without it. – Dr Y Wit Sep 27 '17 at 11:08
Stupendous, I appreciate from the bottom of my heart, this is fantastic. Had this could be achieved with SQL, it would have been great. – Jacob Sep 27 '17 at 11:57
Presumably, I can use a function with the code snippet of yours and call the function from SQL by passing the necessary parameters. – Jacob Sep 27 '17 at 12:09
Yes... but the function called from within SQL would return a [cursor expression](https://stackoverflow.com/q/8637050/1509264) in a single result set row - it would not generate multiple rows. – MT0 Sep 27 '17 at 12:15
@MT0 I have not noticed that, any workaround for this? – Jacob Sep 27 '17 at 12:23
The flippant answer is to hardcode the column names and not use dynamic SQL. If you are calling it from an external language (Java, C#, etc) then you can just directly call the function and parse the returned cursor (without invoking the function via an intermediate SQL step). Oracle 12c has the concept of [implicit statement results](https://oracle-base.com/articles/12c/implicit-statement-results-12cr1) which may also work. – MT0 Sep 27 '17 at 12:37
@MT0 Another approach would be to use this in a procedure and I have an option to execute a trigger before executing a report. So in the procedure, I will populate the values into a temporary table and read the temporary table from the report. – Jacob Sep 27 '17 at 12:37

Dr Y Wit · Answer 2 · 2017-09-27T12:10:49.377

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,
  2         sum(decode(dt, :start_date, tot_hours)) start_date_hours,
  3         sum(decode(dt, :end_date, tot_hours)) end_date_hours,
  4         sum(tot_hours) tot_hours
  5    from (select prod_id, start_date as dt, start_date, tot_hours
  6            from prod_timings t)
  7   group by dt, prod_id
  8   order by dt desc, prod_id;

PROD_ID      DT            START_DATE_HOURS       END_DATE_HOURS  TOT_HOURS
------------ --------- -------------------- -------------------- ----------
PR220        19-SEP-17                                        .0          0
PR2230       19-SEP-17                                       2.0          2
PR9036       19-SEP-17                                        .6         .6
PR9702       19-SEP-17                                       3.0          3
PR7127       18-SEP-17                   .0                               0
PR9036       18-SEP-17                  3.4                             3.4
PR91034      18-SEP-17                  4.0                               4
PR9609       18-SEP-17                  5.0                               5

8 rows selected.

Bizarre solution

SQL> with t as
  2  (select *
  3    from (select prod_id, start_date as dt, start_date, tot_hours
  4            from prod_timings t)
  5  pivot xml(sum(tot_hours) as s for start_date in
  6  (select :start_date from dual union all select :end_date from dual)))
  7  select prod_id, dt, start_date_hours, end_date_hours,
  8         nvl(start_date_hours, end_date_hours) tot_hours
  9  from 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
 16   order by dt desc, prod_id;

PROD_ID      DT            START_DATE_HOURS       END_DATE_HOURS  TOT_HOURS
------------ --------- -------------------- -------------------- ----------
PR220        19-SEP-17                                        .0          0
PR2230       19-SEP-17                                       2.0          2
PR9036       19-SEP-17                                        .6         .6
PR9702       19-SEP-17                                       3.0          3
PR7127       18-SEP-17                   .0                               0
PR9036       18-SEP-17                  3.4                             3.4
PR91034      18-SEP-17                  4.0                               4
PR9609       18-SEP-17                  5.0                               5

8 rows selected.

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

The idea is to avoid hardcoding of the dates in the first SQL statement. Appreciate if you could an example. — Jacob, Sep 27 '17 at 10:03
@user75ponic, thanks for minus. :) I provided you two ways to achieve what you want. If you want to use pivot clause then use pivot xml + subquery but group by would be much simpler in this case. If you do not want to construct and parse XML then pivot will fail with ORA-56900: bind variable is not supported inside pivot|unpivot operation — Dr Y Wit, Sep 27 '17 at 10:38
pivot xml ( sum(tot_hours) for start_date IN (select :start_date from dual union all select :end_date from dual) ) — Dr Y Wit, Sep 27 '17 at 10:43
I did not do the minus or negative marking and I will not certainly do that. You can see my profile comment where I mentioned that I will never downvote. — Jacob, Sep 27 '17 at 12:01
Perhaps you can contact the administrators to trace the logs to seek the negative or downvote history. — Jacob, Sep 27 '17 at 12:05
You do not need to apologize, I am certainly against the downvoting here in Stackoverflow. — Jacob, Sep 27 '17 at 12:13
If the day or date appears as the header instead of START_DATE_HOURS and END_DATE_HOUR, it would have been fantastic. — Jacob, Sep 27 '17 at 12:20
Plese double check my statement about parse stage and sharing plan for binds. — Dr Y Wit, Sep 27 '17 at 12:30

Dynamic Query for PIVOT In Clause

2 Answers2

Linked