对于给定的ID(键)，如何将动态行值转换为oracle11gSQL中的列。需要使用枢轴和和聚合函数

Question

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

我需要的结果如下

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代码非常陌生。我试着玩这个密码已经有一段时间了。需要绝望的帮助，因为我不知道谁是我个人可以接触到的。我非常感谢你从这个社区得到的帮助。这是我的第一个问题和帖子。

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

Answer 1

使用条件(CASE)聚合(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>