Revision: 63855
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
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