Posted By

mprabhuram on 06/13/13

Tagged

sql
Oracle

Versions (?)

Last Edited at 06/13/13 02:45am

Statistics

Viewed 2231 times

Favorited by 0 user(s)

Related snippets

CASE statement to transpose rows in to columns

/ Published in: SQL

This SQL would help you to transpose a rows into columns in SQL

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

SELECT * FROM
(  SELECT h.object_key AS h_object_key,
         h.service AS h_service,
         h.run_seq AS h_run_seq,
         h.start_time AS h_start_time,
         h.end_time AS h_end_time,
         h.execution_time AS h_execution_time,
         h.status AS h_status,
         S.OBJECT_KEY,
         S.KEY2,
         MAX (CASE WHEN S.NAME = 'PATH' THEN S.VALUE ELSE NULL END) AS PATH,
         MAX (CASE WHEN S.NAME = 'OBJECT_NAME' THEN S.VALUE ELSE NULL END)
            AS OBJECT_NAME,
         MAX (CASE WHEN S.NAME = 'OBJECT_TYPE' THEN S.VALUE ELSE NULL END)
            AS OBJECT_TYPE,
         MAX (CASE WHEN S.NAME = 'ROW_COUNT' THEN S.VALUE ELSE NULL END)
            AS ROW_COUNT,
         MAX (CASE WHEN S.NAME = 'START_TIME' THEN S.VALUE ELSE NULL END)
            AS START_TIME,
         MAX (CASE WHEN S.NAME = 'END_TIME' THEN S.VALUE ELSE NULL END)
            AS END_TIME,
         MAX (CASE WHEN S.NAME = 'EXECUTION_TIME' THEN S.VALUE ELSE NULL END)
            AS EXECUTION_TIME,
         MAX (CASE WHEN S.NAME = 'DATAFLOW_NAME' THEN S.VALUE ELSE NULL END)
            AS DATAFLOW_NAME,
         MAX (CASE WHEN S.NAME = 'PARENT_ID' THEN S.VALUE ELSE NULL END)
            AS PARENT_ID,
         MAX (CASE WHEN S.NAME = 'CACHE_SIZE' THEN S.VALUE ELSE NULL END)
            AS CACHE_SIZE,
         MAX (CASE WHEN S.NAME = 'OBJECT_ID' THEN S.VALUE ELSE NULL END)
            AS OBJECT_ID
 
    FROM al_history h LEFT JOIN al_statistics s ON h.object_key = s.object_key
   WHERE 
   h.start_time  > to_date('05/31/2013 12:00:00 AM','mm/dd/yyyy hh:mi:ss am')
GROUP BY h.object_key,
         h.service,
         h.run_seq,
         h.start_time,
         h.end_time,
         h.execution_time,
         h.status,
         S.OBJECT_KEY,
         S.KEY2
ORDER BY S.KEY2
)
WHERE OBJECT_TYPE = 'Dataflow'