基于时间戳数据枢轴的PlSQL动态查询

Question

我想要创建一个动态SQL查询，以查看列中的日期，日期行将继续每天添加，因此查询必须是动态的。我希望一些列在行中，而有些行必须是列，Pivot函数将工作，但是我需要一些帮助来创建这个查询。

数据：-

 PLANNER_ID|ASSIGNED|TODO|DONE|REJECTED|BLOCKING_PROJECTS|AVERAGE|WORKING_DAYS|DATES
 Planner 1 |25      |0   |0   |0       |0                |0%     |1           |2018.11.26
 Planner 1 |18      |0   |0   |0       |0                |0%     |1           |2018.11.21
 Planner 1 |26      |0   |0   |0       |0                |0%     |1           |2018.11.25
 Planner 1 |25      |0   |0   |0       |0                |0%     |1           |2018.11.28
 Planner 1 |25      |0   |0   |0       |0                |0%     |1           |2018.11.27
 Planner 1 |26      |0   |0   |0       |0                |0%     |1           |2018.11.24
 Planner 1 |25      |0   |0   |0       |0                |0%     |1           |2018.11.29

需要的输出为：-

  PLANNER_ID|PROJECTS          |21-NOV-2018|24-NOV-2018|25-NOV-2018|26-NOV-2018|27-NOV-2018|28-NOV-2018|29-NOV-2018
  Planner 1 |ASSIGNED          |0          |0          |0          |0          |0          |0          |0
  Planner 1 |TODO              |0          |0          |0          |0          |0          |0          |0
  Planner 1 |AVERAGE           |0%         |0%         |0%         |0%         |0%         |0%         |0%
  Planner 1 |BLOCKING_PROJECTS |0          |0          |0          |0          |0          |0          |0
  Planner 1 |DONE              |0          |0          |0          |0          |0          |0          |0
  Planner 1 |REJECTED          |0          |0          |0          |0          |0          |0          |0
  Planner 1 |WORKING_DAYS      |1          |1          |1          |1          |1          |1          |1

Answer 1

下面是一个示例查询。

WITH t AS (
  SELECT *
    FROM (
           SELECT 'Planner 2' AS planner_id, '15' AS assigned, '10' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '26%' AS average, '1' AS working_days, '2018.11.26' AS dates FROM dual UNION ALL
           SELECT 'Planner 2' AS planner_id, '18' AS assigned, '12' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '21%' AS average, '1' AS working_days, '2018.11.21' AS dates FROM dual UNION ALL
           SELECT 'Planner 2' AS planner_id, '16' AS assigned, '13' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '25%' AS average, '1' AS working_days, '2018.11.25' AS dates FROM dual UNION ALL
           SELECT 'Planner 2' AS planner_id, '15' AS assigned, '14' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '28%' AS average, '1' AS working_days, '2018.11.28' AS dates FROM dual 
           UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '1' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '26%' AS average, '1' AS working_days, '2018.11.26' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '28' AS assigned, '2' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '21%' AS average, '1' AS working_days, '2018.11.21' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '26' AS assigned, '3' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '25%' AS average, '1' AS working_days, '2018.11.25' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '4' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '28%' AS average, '1' AS working_days, '2018.11.28' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '5' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '27%' AS average, '1' AS working_days, '2018.11.27' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '26' AS assigned, '6' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '24%' AS average, '1' AS working_days, '2018.11.24' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '7' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '29%' AS average, '1' AS working_days, '2018.11.29' AS dates FROM dual 
         )
)
SELECT *
  FROM (
         SELECT  planner_id, projects, dates, quantity
           FROM t
        UNPIVOT (quantity FOR projects IN (assigned AS 'ASSIGNED', todo AS 'TODO', done AS 'DONE', rejected AS 'REJECTED', blocking AS 'BLOCKING', average AS 'AVERAGE', working_days AS 'WORKING_DAYS'))
       )
 PIVOT  (MAX(quantity) FOR (dates) IN ('2018.11.26'  AS "2018.11.26" , '2018.11.21'  AS "2018.11.21" , '2018.11.25'  AS "2018.11.25" , '2018.11.28'  AS "2018.11.28" , '2018.11.27'  AS "2018.11.27" , '2018.11.24'  AS "2018.11.24" , '2018.11.29' AS "2018.11.29"))
ORDER BY planner_id, projects;

如果日期列中的值列表不是常量或很大，则必须以动态方式生成此查询。您必须生成枢轴的日期列表。

DECLARE
  lv_query CLOB :=
q'[WITH t AS (
  SELECT *
    FROM (
           SELECT 'Planner 2' AS planner_id, '15' AS assigned, '10' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '26%' AS average, '1' AS working_days, '2018.11.26' AS dates FROM dual UNION ALL
           SELECT 'Planner 2' AS planner_id, '18' AS assigned, '12' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '21%' AS average, '1' AS working_days, '2018.11.21' AS dates FROM dual UNION ALL
           SELECT 'Planner 2' AS planner_id, '16' AS assigned, '13' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '25%' AS average, '1' AS working_days, '2018.11.25' AS dates FROM dual UNION ALL
           SELECT 'Planner 2' AS planner_id, '15' AS assigned, '14' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '28%' AS average, '1' AS working_days, '2018.11.28' AS dates FROM dual 
           UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '1' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '26%' AS average, '1' AS working_days, '2018.11.26' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '28' AS assigned, '2' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '21%' AS average, '1' AS working_days, '2018.11.21' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '26' AS assigned, '3' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '25%' AS average, '1' AS working_days, '2018.11.25' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '4' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '28%' AS average, '1' AS working_days, '2018.11.28' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '5' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '27%' AS average, '1' AS working_days, '2018.11.27' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '26' AS assigned, '6' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '24%' AS average, '1' AS working_days, '2018.11.24' AS dates FROM dual UNION ALL
           SELECT 'Planner 1' AS planner_id, '25' AS assigned, '7' AS todo, '0' AS done, '0' AS rejected, '0' AS blocking, '29%' AS average, '1' AS working_days, '2018.11.29' AS dates FROM dual 
         )
)
SELECT *
  FROM (
         SELECT  planner_id, projects, dates, quantity
           FROM t
        UNPIVOT (quantity FOR projects IN (assigned AS 'ASSIGNED', todo AS 'TODO', done AS 'DONE', rejected AS 'REJECTED', blocking AS 'BLOCKING', average AS 'AVERAGE', working_days AS 'WORKING_DAYS'))
       )
 PIVOT  (MAX(quantity) FOR (dates) IN (#PivotDates))
ORDER BY planner_id, projects]';

  /*
  *  Generate list of dates for PIVOT
  */
  FUNCTION pivotDates
  RETURN CLOB
  IS
  BEGIN
    RETURN yourPivotDates;
  END pivotDates;
BEGIN

  lv_query := REPLACE(lv_query, '#PivotDates', pivotDates());

  EXECUTE IMMEDIATE lv_query;
END;
/