For given ID(Key), How to convert dynamic rows values into columns in oracle 11g SQL. Need to use PI-CodePudding

ID        PaymentLateBy        Amount

1111      30 days over        1000
1111      90 days over        3000
1111      90 days over        2000
1112      45 days over         100
1113      120 days over        900
1113      On Schedule         1000
1113      120 days over        500

Results I need is as following

ID        On_Schedule  30_days_over   45_days_over  90_days_over 120_days_over    

1111      0            1000            0            5000         0
1112      0            0               100          0            0
1113      1000         0               0            0            1400

I am very new to coding SQL. I have been trying to play with this code for sometime. Need desperate help as I do not know anyone personally whom I can reach out for. I would really appreciate your help from this community. This is my first question and post.

select * from (
select t.ID, t.PaymentLateBy, t.Amount
      from tbl1 t 
)PIVOT
( 
 sum(t.Amount)
 FOR t.PaymentLateBy IN ('On Schedule', '30 days over', '45 days over', '60 days over', '90 days over', '90  days over', '120 days over','120  days over')
)
ORDER BY t.PaymentLateBy

CodePudding user response：

You can use:

select id,
       COALESCE(on_schedule, 0) AS "on schedule",
       COALESCE(days30, 0)      AS "30 days over",
       COALESCE(days45, 0)      AS "45 days over",
       COALESCE(days90, 0)      AS "90 days over",
       COALESCE(days120, 0)     AS "120 days over"       
from   tbl1
PIVOT( 
  SUM(Amount)
  FOR PaymentLateBy IN (
    'On Schedule'   AS on_schedule,
    '30 days over'  AS days30,
    '45 days over'  AS days45,
    '90 days over'  AS days90,
    '120 days over' AS days120
  )
)
ORDER BY id;

Which, for your sample data:

CREATE TABLE tbl1 (ID, PaymentLateBy, Amount) AS
SELECT 1111, '30 days over',  1000 FROM DUAL UNION ALL
SELECT 1111, '90 days over',  3000 FROM DUAL UNION ALL
SELECT 1111, '90 days over',  2000 FROM DUAL UNION ALL
SELECT 1112, '45 days over',   100 FROM DUAL UNION ALL
SELECT 1113, '120 days over',  900 FROM DUAL UNION ALL
SELECT 1113, 'On Schedule',   1000 FROM DUAL UNION ALL
SELECT 1113, '120 days over',  500 FROM DUAL;

Outputs:

ID on schedule 30 days over 45 days over 90 days over 120 days over

1111 0 1000 0 5000 0

1112 0 0 100 0 0

1113 1000 0 0 0 1400

ID	on schedule	30 days over	45 days over	90 days over	120 days over
1111	0	1000	0	5000	0
1112	0	0	100	0	0
1113	1000	0	0	0	1400

db<>fiddle here

CodePudding user response：

Using conditional (CASE) aggregation (SUM):

SQL> select id,
  2    sum(case when paymentlateby = 'On Schedule'  then amount else 0 end) on_schedule,
  3    sum(case when paymentlateby = '30 days over' then amount else 0  end) "30 days over",
  4    sum(case when paymentlateby = '45 days over' then amount else 0  end) "45 days over",
  5    sum(case when paymentlateby = '90 days over' then amount else 0  end) "90 days over",
  6    sum(case when paymentlateby = '120 days over' then amount else 0  end) "120 days over"
  7  from tbl1
  8  group by id
  9  order by id;

        ID ON_SCHEDULE 30 days over 45 days over 90 days over 120 days over
---------- ----------- ------------ ------------ ------------ -------------
      1111           0         1000            0         5000             0
      1112           0            0          100            0             0
      1113        1000            0            0            0          1400

SQL>