CODEWITHSUNDEEP
sqloracle-databasepivot
doomdaam
doomdaam

Reputation: 783

Group By and move time columns side by side

I have the following query that returns the below table. The output is showing when my procedure to disabling triggers starts and when the disabling procedure ends and the same for enabling the triggers.

Don't worry about the times, the procedure is very fast on a small dataset.

select  
    job_ID ,
    PROCEDURE_NAME ,
    CAST(DATE_INSERTED AS TIMESTAMP) AS END_TIME ,
    TRIM(SUBSTR(MESSAGE, -12)) AS MSG,
    STEP_NAME
from CON_FRAMEWORK_LOG;

| JobID |    PROCEDURE_NAME     | END_TIME |      MSG    | STEP_NAME|   
| ----- | --------------------- | -------- | ----------- | -------- |
| 20537| DISABLE_ENABLE_TRIGGERS| 12:55:49 | DISABLE mode| Start|
| 20537| DISABLE_ENABLE_TRIGGERS| 12:55:49 | DISABLE mode| End  |
| 20537| DISABLE_ENABLE_TRIGGERS| 12:56:07 | ENABLE mode | Start|
| 20537| DISABLE_ENABLE_TRIGGERS| 12:56:08 | ENABLE mode | End  |

The goal is to adjust the query to return the following table:

| JobID |    PROCEDURE_NAME     | START_TIME | END_TIME   |     MSG      |    
| ----- | --------------------- | --------   | ---------- | ------------ | 
| 20537| DISABLE_ENABLE_TRIGGERS| 12:55:49   | 12:55:49   | DISABLE mode | 
| 20537| DISABLE_ENABLE_TRIGGERS| 12:56:07   | 12:56:08   | ENABLE mode  |

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use conditional aggregation:

select job_ID, PROCEDURE_NAME ,
       max(case when step_name = 'start' then CAST(DATE_INSERTED AS TIMESTAMP) end) as start_time,
       max(case when step_name = 'end' then CAST(DATE_INSERTED AS TIMESTAMP) end) as end_time,
       TRIM(SUBSTR(MESSAGE, -12)) AS MSG,
       STEP_NAME
from (select fl.*,
             row_number() over (partition by job_id, procedure_name, step_name, TRIM(SUBSTR(MESSAGE, -12)) order by date_inserted) as seqnum
      from CON_FRAMEWORK_LOG fl
     ) fl
group by job_id, procedure_name, step_name,
         TRIM(SUBSTR(MESSAGE, -12)), seqnum

Upvotes: 2

Related Questions