原帖由 atgc 于 2007-11-20 14:06 发表
SQL> select * from test;
ID BZ
JE
---------- --- ----------
1 USD100
1 RMB200
1 HKD300
1 SGD400
...
根据atgc的给出的代码,我尝试了一下将USD、RMB、HKD同时旋转的写法如下所示,稍微麻烦了点^_^
需要注意使用分析函数lag时的顺序问题,这个跟最后一步的dedoce中的计算应该刚好是逆序的
select id,
decode(bz3, '0USD', je3, null),
decode(bz2, '1HKD', je2, null),
decode(bz1, '2RMB', je1, null),
bz,
je
from (select id,
bz,
je,
lAG(je, 1) over(partition by id order by bz) je1,
lAG(bz, 1) over(partition by id order by bz) bz1,
lag(je, 2) over(partition by id order by bz) je2,
lag(bz, 2) over(partition by id order by bz) bz2,
lag(je, 3) over(partition by id order by bz) je3,
lag(bz, 3) over(partition by id order by bz) bz3
from (select id,
decode(bz,
'USD',
'0' || bz,
'HKD',
'1' || bz,
'RMB',
'2' || bz,
bz) bz,
sum(je) je
from test
group by id, bz
order by bz))
where bz not in ('0USD', '1HKD', '2RMB') |