[Oracle备课资料]Oracel行转列问题（rownum，full join，decode、max函数的使用）

测试数据如下：

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;