Posted By

mprabhuram on 06/13/13


Tagged

sql Oracle Transpose


Versions (?)

CASE statement to transpose rows in to columns


 / Published in: SQL
 

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

  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  

You need to login to post a comment.