The SQL PIVOT command can be used to show the summarised amounts/values of an aggregated query (GROUP BY…) as columns, rather than grouped rows. Here’s an example, in HR, to show total amounts in specific pay elements, per employee/assignment (assignment_id):
SELECT assignment_id , NVL(gen,0) gen , NVL(car,0) car , NVL(study,0) study , NVL(study_subs,0) study_subs , NVL(acting,0) acting , NVL(parking,0) parking , NVL(retire,0) retire , NVL(medical,0) medical FROM ( SELECT ee.assignment_id , et.element_name , SUM(TO_NUMBER(ev.screen_entry_value)) val FROM pay_element_types_f_tl et , pay_element_links_f el , pay_element_entries_f ee , pay_element_entry_values_f ev , pay_input_values_f iv WHERE et.element_type_id = el.element_type_id AND el.element_link_id = ee.element_link_id AND ee.element_entry_id = ev.element_entry_id AND ev.input_value_id = iv.input_value_id AND TRUNC(SYSDATE) BETWEEN el.effective_start_date AND el.effective_end_date AND TRUNC(SYSDATE) BETWEEN ee.effective_start_date AND ee.effective_end_date AND TRUNC(SYSDATE) BETWEEN ev.effective_start_date AND ev.effective_end_date AND TRUNC(SYSDATE) BETWEEN iv.effective_start_date AND iv.effective_end_date AND et.element_name IN ('Gen Allowance' ,'Car Allow' ,'Professional Study Allowance' ,'Professional Study Subscription' ,'Acting Up' ,'Car Parking Space Paid' ,'Retirement Allowance' ,'Medical Insurance' ) GROUP BY ee.assignment_id , et.element_name ) PIVOT ( SUM(val) FOR element_name IN ('Gen Allowance' gen ,'Car Allow' car ,'Professional Study Allowance' study ,'Professional Study Subscription' study_subs ,'Acting Up' acting ,'Car Parking Space Paid' parking ,'Retirement Allowance' retire ,'Medical Insurance' medical ) ) ORDER BY 1,2 ;