如何使用透视在Oracle中表示请求提交和审批数据?

rta7y2nd  于 2023-06-22  发布在  Oracle
关注(0)|答案(1)|浏览(96)

我有请求提交及其审批过程历史的数据集。我需要代表其提交顺序日期和最终批准和拒绝日期的每个请求编号。就像我有这样的enter image description here附加图像数据集。我想显示它如下图所示。enter image description here
我尝试了透视,但无法得到它,因为透视只对聚合函数起作用

wwwo4jvm

wwwo4jvm1#

这个查询应该可以做到这一点:

SELECT
req_num,
MAX(CASE WHEN rn = 1 AND action = "SUBMIT" THEN action_date END) AS "1st submission",
MAX(CASE WHEN rn = 2 AND action = "SUBMIT" THEN action_date END) AS "2nd submission",
MAX(CASE WHEN rn = 3 AND action = "SUBMIT" THEN action_date END) AS "3rd submission",
-- Add more MAX(CASE WHEN rn = N THEN action_date END) for additional submissions
MAX(CASE WHEN action = 'APPROVAL' THEN action_date END) AS max_approval,
MAX(CASE WHEN action = 'REJECT' THEN action_date END) AS reject 
FROM
(
SELECT
    req_num,
    action_date,
    ROW_NUMBER() OVER (PARTITION BY req_num ORDER BY action_date) AS rn,
    action
FROM
    requests
) sub
GROUP BY req_num;

相关问题