Using SQL PIVOT

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
;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s