动态查询子句中的PIVOT [英] 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,
                 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

我可以使用下面的查询在PIVOT的IN子句中使用动态日期吗?想法是在IN子句中有一个动态查询，以避免对日期进行硬编码

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))

绑定值

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

上面的输出是

19-Sep-17
18-Sep-17

预期输出为

╔═════════╦════════════╦════════╦════════╦═══════════╗
║ 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         ║
╚═════════╩════════════╩════════╩════════╩═══════════╝

表的结构和值

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;

推荐答案

类似的东西

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！