我有这张表:
create table history (
date_check DATE,
type VARCHAR2(30),
id_type NUMBER,
total NUMBER
)
正在选择......
select * from history order by 1
DATE_CHECK TYPE ID_TYPE TOTAL
14/02/2016 abc 1 14
14/02/2016 abc33 1 14
14/02/2016 bbb 1 40
14/02/2016 bbb33 3 43
14/02/2016 ddd 2 61
14/02/2016 ddd33 2 62
15/02/2016 abc 1 33
15/02/2016 abc33 1 44
15/02/2016 bbb 1 55
15/02/2016 bbb33 3 66
15/02/2016 ddd 2 77
15/02/2016 ddd33 2 88
始终输入这 6 个值:
abc
abc33
bbb
bbb33
ddd
ddd33
我将这些数据与“id_type”交叉,因此有如下解码:
select type || decode(id_type, 1, '- new', 2, '- old', 3, '- xpto') as type from history order by 1
最后我需要这样的东西:
DATE_CHECK abc - new abc33 - old bbb - new bbb33 - old ....
14/02/2016 14 14 40 43
15/02/2016 33 44 55 66
最简单的方法是什么?使用枢轴?
尝试这个:
with data as(
select date_check, type, total from (
select date_check, type || ' ' || decode(id_type, 1, '- new', 2, '- old', 3, '- xpto') as type, total from history
))
select * from data
pivot(
max(total) for type in ('abc - new', 'abc33 - new', 'bbb - new',
'bbb33 - xpto', 'ddd - old', 'ddd33 - old')
)
order by date_check;
对于“反之亦然”的使用UNPIVOT https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1#unpivot
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)