表结构如下:
t_fl:
lb
----
a
b
c
d
et_sj
id lb sl
-------------
1 a 10
1 c 30
1 d 40
1 e 50
2 b 20
2 e 50想弄出这样一个结果:
id sl_a sl_b sl_c sl_d sl_e
---------------------------------
1 10 30 40 50
2 20 50如何才能实现啊?
t_fl:
lb
----
a
b
c
d
et_sj
id lb sl
-------------
1 a 10
1 c 30
1 d 40
1 e 50
2 b 20
2 e 50想弄出这样一个结果:
id sl_a sl_b sl_c sl_d sl_e
---------------------------------
1 10 30 40 50
2 20 50如何才能实现啊?
select id,max(decode(lb,'a',sl)) sl_a,
max(decode(lb,'b',sl)) sl_b,max(decode(lb,'c',sl)) sl_c,
max(decode(lb,'d',sl)) sl_d,max(decode(lb,'e',sl)) sl_e
from t_sj group by id order by id;
sum(decode(lb,'b',sl)) sl_b,sum(decode(lb,'c',sl)) sl_c,
sum(decode(lb,'d',sl)) sl_d,sum(decode(lb,'e',sl)) sl_e
from t_sj group by id order by id;
SQL> select id,sum(decode(lb,'a',sl)) sl_a,
2 sum(decode(lb,'b',sl)) sl_b,sum(decode(lb,'c',sl)) sl_c,
3 sum(decode(lb,'d',sl)) sl_d,sum(decode(lb,'e',sl)) sl_e
4 from t_sj group by id order by id; ID SL_A SL_B SL_C SL_D SL_E
---------- ---------- ---------- ---------- ---------- ----------
1 10 30 40 50
2 20 50SQL>
max(decode(lb,'b',sl)) sl_b,max(decode(lb,'c',sl)) sl_c,
max(decode(lb,'d',sl)) sl_d,max(decode(lb,'e',sl)) sl_e
from t_sj group by id order by id;
CREATE OR REPLACE PROCEDURE row2col(o OUT SYS_REFCURSOR) IS
sqlstr VARCHAR2(4000) := '';
BEGIN
FOR cc IN (SELECT lb FROM t_fl) LOOP
sqlstr := sqlstr || 'sum(decode(lb,''' || cc.lb ||''',sl)) as "sl_'
|| cc.lb || '",';
END LOOP;
sqlstr := 'select id,' || rtrim(sqlstr,',') || ' from t_sj group by id';
OPEN o FOR sqlstr;
END row2col;