我想创建一个动态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
下面是一个查询示例。
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;
若DATES列中的值列表不是常量或很大，那个么就必须以动态方式生成这个查询。您必须为PIVOT 生成日期列表
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;
/