How to represent request submission and approval data in Oracle using pivoting?

Question

I have data set for request submission and its approval process history. I need to represent each request number with its submission sequence date and final approval and reject date. Like I have data set like this enter image description here attached image. and I want to display it as shown in below image. enter image description here

I tried pivoting but couldn't get it because pivot will work on only aggregate function

Answer 1

This query should do it:

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;