Advanced Search

CASE statement to transpose rows in to columns

/ Published in: SQL
Save to your folder(s)
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 
  1. SELECT * FROM
  2. (  SELECT h.object_key AS h_object_key,
  3.          h.service AS h_service,
  4.          h.run_seq AS h_run_seq,
  5.          h.start_time AS h_start_time,
  6.          h.end_time AS h_end_time,
  7.          h.execution_time AS h_execution_time,
  8.          h.status AS h_status,
  9.          S.OBJECT_KEY,
  10.          S.KEY2,
  11.          MAX (CASE WHEN S.NAME = 'PATH' THEN S.VALUE ELSE NULL END) AS PATH,
  12.          MAX (CASE WHEN S.NAME = 'OBJECT_NAME' THEN S.VALUE ELSE NULL END)
  13.             AS OBJECT_NAME,
  14.          MAX (CASE WHEN S.NAME = 'OBJECT_TYPE' THEN S.VALUE ELSE NULL END)
  15.             AS OBJECT_TYPE,
  16.          MAX (CASE WHEN S.NAME = 'ROW_COUNT' THEN S.VALUE ELSE NULL END)
  17.             AS ROW_COUNT,
  18.          MAX (CASE WHEN S.NAME = 'START_TIME' THEN S.VALUE ELSE NULL END)
  19.             AS START_TIME,
  20.          MAX (CASE WHEN S.NAME = 'END_TIME' THEN S.VALUE ELSE NULL END)
  21.             AS END_TIME,
  22.          MAX (CASE WHEN S.NAME = 'EXECUTION_TIME' THEN S.VALUE ELSE NULL END)
  23.             AS EXECUTION_TIME,
  24.          MAX (CASE WHEN S.NAME = 'DATAFLOW_NAME' THEN S.VALUE ELSE NULL END)
  25.             AS DATAFLOW_NAME,
  26.          MAX (CASE WHEN S.NAME = 'PARENT_ID' THEN S.VALUE ELSE NULL END)
  27.             AS PARENT_ID,
  28.          MAX (CASE WHEN S.NAME = 'CACHE_SIZE' THEN S.VALUE ELSE NULL END)
  29.             AS CACHE_SIZE,
  30.          MAX (CASE WHEN S.NAME = 'OBJECT_ID' THEN S.VALUE ELSE NULL END)
  31.             AS OBJECT_ID
  32.  
  33.     FROM al_history h LEFT JOIN al_statistics s ON h.object_key = s.object_key
  34.    WHERE 
  35.    h.start_time  > to_date('05/31/2013 12:00:00 AM','mm/dd/yyyy hh:mi:ss am')
  36. GROUP BY h.object_key,
  37.          h.service,
  38.          h.run_seq,
  39.          h.start_time,
  40.          h.end_time,
  41.          h.execution_time,
  42.          h.status,
  43.          S.OBJECT_KEY,
  44.          S.KEY2
  45. ORDER BY S.KEY2
  46. )
  47. WHERE OBJECT_TYPE = 'Dataflow'

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.