EN VI

How can I use oracle sql pivot to convert multiple rows into one based on one column?

How can I use oracle sql pivot to convert multiple rows into one based on one column

I have this challenge in oracle sql. Say I have this table TABLE_1 (which comes from a subquery):

EMPLOYEE       ELEMENT        VALUE      DEPT
001            Basic           100       Sales
001            Rent            300       Sales
002            Basic           200       Procurement
002            Basic           100       Procurement
003            Pension         400       Procurement

Now I want to convert it to look like this:

EMPLOYEE      Basic      Rent     Pension        DEPT
001           100        300       0             Sales
002           300        0         0             Procurement
003           0          0         400           Procurement

So even though there were multiple lines for each employee, I want just one per employee and then the sum of each payroll element as a column which will be set to zero if nothing is available for the employee.

Solution:

You need a conditional aggregation query for this -

WITH data AS (SELECT '001' EMPLOYEE, 'Basic' ELEMENT, 100 VALUE, 'Sales' DEPT FROM dual UNION ALL
              SELECT '001', 'Rent', 300, 'Sales' FROM dual UNION ALL
              SELECT '002', 'Basic', 200, 'Procurement' FROM dual UNION ALL
              SELECT '002', 'Basic', 100, 'Procurement' FROM dual UNION ALL
              SELECT '003', 'Pension', 400, 'Procurement' FROM dual
             )
SELECT employee, dept, 
       NVL(SUM(CASE WHEN ELEMENT = 'Basic' THEN value END), 0) basic,
       NVL(SUM(CASE WHEN ELEMENT = 'Rent' THEN value END), 0) Rent,
       NVL(SUM(CASE WHEN ELEMENT = 'Pension' THEN value END), 0) Pension
  FROM data
 GROUP BY employee, dept;

Demo.