多行转一行的问题

xxwood 2010-01-27 05:00:21

表结构如下：
t_fl：
lb
----
a
b
c
d
e

t_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

如何才能实现啊？

...全文

165 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

xxwood 2010-01-28

打赏
举报

高人无处不在！谢了！

tangren 2010-01-27

打赏
举报

--如果t_fl表行数据不定的话可以写一个存储过程

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;

Phoenix_99 2010-01-27

打赏
举报

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;

crazylaa 2010-01-27

打赏
举报

select id,sum(decode(lb,'a',sl)) sl_a,
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                               50



SQL>

liusong_china 2010-01-27

打赏
举报



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;