Return to Snippet

Revision: 63855
at June 13, 2013 02:45 by mprabhuram


Initial Code
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'

Initial URL

                                

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

Initial Title
CASE statement to transpose rows in to columns

Initial Tags
sql, Oracle

Initial Language
SQL