请教各位问一个行列转换问题,怎么实现?
表A
PROVINCE PROVINCECODE ORDERBY
北京 bj 01
上海 sh 02
天津 tj 03
.......
西藏 xz 31表B
PROVINCECODE IP_SUM LOGIN_NUM
bj 100 30
sh 99 9
.......
xj 10 3
xz 0 0现要求转换为bj 100 300 sh 99 9 ..... xz 0 0其中表B的记录不重复该怎么实现呢,先谢谢大家了
表A
PROVINCE PROVINCECODE ORDERBY
北京 bj 01
上海 sh 02
天津 tj 03
.......
西藏 xz 31表B
PROVINCECODE IP_SUM LOGIN_NUM
bj 100 30
sh 99 9
.......
xj 10 3
xz 0 0现要求转换为bj 100 300 sh 99 9 ..... xz 0 0其中表B的记录不重复该怎么实现呢,先谢谢大家了
如果是固定的
就用max(decode( PROVINCECODE ,'bj',IP_SUM ,0)) bj_IP_SUM 来实现
就是烦点,要写可能90多个decode
如果不固定的,写存储过程实现吧
from
(
select connect_by_isleaf aaa,sys_connect_by_path(provincecode||ip_sum||login_num, '-') as bbb
from
(
select b.provincecode, b.ip_sum, b.login_num,rownum c2,rownum-1 c1
from tbla a, tblb b
where a.provincecode = b.provincecode
order by a.orderby
)c
start with c1 = '0' connect by prior c2 = c1
)
where aaa=1;
from
(
select connect_by_isleaf aaa,sys_connect_by_path(provincecode | |ip_sum | |login_num, '-') as bbb
from
(
select b.provincecode, b.ip_sum, b.login_num,rownum c2,rownum-1 c1
from tbla a, tblb b
where a.provincecode = b.provincecode
order by a.orderby
)c
start with c1 = '0' connect by prior c2 = c1
)
where aaa=1;