测试数据如下:
create table t_mp_test
(
kpi_id varchar2(20),
trade_dt varchar2(10),
sum_value number(20,2),
sum_value_lst_d number(20,2)
);
insert into t_mp_test values('C_ACCT_001','2015-09-05',0,0);
insert into t_mp_test values('C_ACCT_005','2015-08-31',206,0);
insert into t_mp_test values('C_ACCT_006','2015-08-31',416,0);
commit;
解答1:
select a.trade_dt C_ACCT_001日期,
a.sum_value C_ACCT_001余额,
a.sum_value_lst_d C_ACCT_001日均余额,
b.trade_dt C_ACCT_005日期,
b.sum_value C_ACCT_005余额,
b.sum_value_lst_d C_ACCT_005日均余额
from
(select rownum rn,trade_dt,sum_value,sum_value_lst_d from t_mp_test where kpi_id='C_ACCT_001') a full join
(select rownum rn,trade_dt,sum_value,sum_value_lst_d from t_mp_test where kpi_id='C_ACCT_005') b on a.rn=b.rn
解答2:
select max(decode(kpi_id,'C_ACCT_001',trade_dt)) as C_ACCT_001日期,
2 max(decode(kpi_id,'C_ACCT_001',sum_value)) as C_ACCT_001余额,
3 max(decode(kpi_id,'C_ACCT_001',sum_value_lst_d)) as C_ACCT_001日均余额,
4 max(decode(kpi_id,'C_ACCT_005',trade_dt)) as C_ACCT_005日期,
5 max(decode(kpi_id,'C_ACCT_005',sum_value)) as C_ACCT_005余额,
6 max(decode(kpi_id,'C_ACCT_005',sum_value_lst_d)) as C_ACCT_005日均余额
7 from
8 t_mp_test;