CODEWITHSUNDEEP
sqloracle-databasepivot-tableoracle-sqldeveloper
acroniks
acroniks

Reputation: 109

Rename Column name in Pivot query and format its time value in format HH:MM:SS

I am preparing one report for which I need to have sum of time taken by various processes in column 2.

I am sharing small snippet in which I am not able to figure out. Below is the query.

SELECT DISTINCT *
FROM CTE_NAME
PIVOT ( SUM(TIME) FOR ACTIVITY IN ( NULL
,Process 1
,Process 2
    )
) order by 2 desc

Output of my query

Things to be noted:-

  1. hours_diff is the value calculated in a CTE expression above this shared snippet
  2. hours_diff is of date type.

Things to be done:-

  1. It is required to change the Column name NULL to TOTAL_TIME

  2. It is required to convert the value of TIME to HH:MM:SS

Things I have tried:-

  1. I tried using SUM(TIME) AS TOTAL_TIME ... it didn't seem to work. Also after that I tried replacing NULL with TOTAL_TIME but it changed in a way that TIME values of Process 1 shifted towards column 2.

  2. In order to convert the time value (which is in seconds). I have this previously used TO_CHAR(TRUNC(sec_diff/3600),'FM9900') || ':' || TO_CHAR(TRUNC(MOD(sec_diff,3600)/60),'FM00') || ':' || TO_CHAR(MOD(sec_diff,60),'FM00') as time_diff. But as I am unable to fetch the column from above snippet I am not able to convert the values to HH:MM:SS

  3. If I convert the value of TIME inside CTE then SUM function in SELECT query throws error Invalid Number as the value being passed to SUM function is in format HH:MM:SS

Upvotes: 1

Views: 208

Answers (1)

Popeye
Popeye

Reputation: 35910

For giving name to the column, you can provide name in IN list of PIVOT as follows:

SELECT DISTINCT *
FROM CTE_NAME
PIVOT ( SUM(TIME) FOR ACTIVITY
 IN ( NULL as TOTAL_TIME, -- THIS
,Process 1
,Process 2
    )
) order by 2 desc;

To calculate HH:MI:SS from seconds, use this in select.(works for seconds in diff is less than 86400(1 day)

TO_CHAR(TRUNC(SYSDATE) + INTERVAL '1' SECOND * YOUR_COLUMN_SECONDS, 'HH24:MI:SS')

Or following will work for any bigger values

Lpad(Trunc(your_column_seconds/86400),2,'0') 
|| ':' || 
Lpad(Trunc(your_column_seconds/1440),2,'0') 
|| ':' || 
Lpad(Trunc(your_column_seconds/60),2,'0')

Note: column name of seconds difference generated by pivot should be used in above expression.

Upvotes: 1

Related Questions