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