请问如何把这纵向的数据转成横向呀？

我怎么觉得一句SQL不能解决问题。考虑function。
对了，你的classID是变化的么？还是固定12、13、14，如果固定，是可以做的。

看不到图,贴个昨天写的比较蛋疼的SQL给你参考
SELECT     LEVEL,
           REGEXP_SUBSTR
                     ('first,second,third',
                      '[^,]*',
                        NVL (REGEXP_INSTR ('first,second,third',
                                           ',',
                                           1,
                                           DECODE (LEVEL - 1,
                                                   0, NULL,
                                                   LEVEL - 1
                                                  )
                                          ),
                             0
                            )
                      + 1
                     ) split_result
      FROM DUAL
CONNECT BY NVL (REGEXP_INSTR ('first,second,third',
                              ',',
                              1,
                              DECODE (LEVEL - 1, 0, NULL, LEVEL - 1)
                             ),
                1
               ) > 0

with tb1 as(
select 61520528 id,12 classid,0 a,0 b,0 c from dual union all
select 61520528 id,13 classid,0 a,0 b,0 c from dual union all
select 61520528 id,14 classid,1 a,1 b,5 c from dual
)
select id,
       max(decode(classid,12,a,0)) a12,max(decode(classid,12,b,0)) b12,max(decode(classid,12,c,0)) c12,
       max(decode(classid,13,a,0)) a13,max(decode(classid,13,b,0)) b13,max(decode(classid,13,c,0)) c13,
       max(decode(classid,14,a,0)) a14,max(decode(classid,14,b,0)) b14,max(decode(classid,14,c,0)) c14
from tb1
group by id;

        ID        A12        B12        C12        A13        B13        C13        A14        B14        C14
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  61520528          0          0          0          0          0          0          1          1          5

谢谢你，这个方法可以。对了，还有一种情况，这边我只举了"12,13,14",如果有6列如“12，13,14,15,16,17”，就是说如果类型CLASSID比较多的话怎么办，是不是也这样的写,有没有简洁的方法，感觉写下去SELECT这边的代码好多呀

有表TBL字段id,classid,a,b,c，如何根据classid转成下面的数据
原来：
期望：
我换了个地方

select   MAX (DECODE (classid, 11, senditem, 0)) senditem1,
         MAX (DECODE (classid, 11, sendordercount, 0)) sendordercount1,
         MAX (DECODE (classid, 11, totalsendnum, 0)) totalsendnum1,
         MAX (DECODE (classid, 11, totalsendmoney, 0)) totalsendmoney1,
         MAX (DECODE (classid, 11, sendcustnum, 0)) sendcustnum1,
         MAX (DECODE (classid, 11, orderitem, 0)) orderitem1,
         MAX (DECODE (classid, 11, ordercount, 0)) ordercount1,
         MAX (DECODE (classid, 11, totalordernum, 0)) totalordernum1,
         MAX (DECODE (classid, 11, totalordermoney, 0)) totalordermoney1,
         MAX (DECODE (classid, 11, ordercustnum, 0)) ordercustnum1,
         MAX (DECODE (classid, 11, vscustnum, 0)) vscustnum1,
         MAX (DECODE (classid, 12, senditem, 0)) senditem2,
         MAX (DECODE (classid, 12, sendordercount, 0)) sendordercount2,
         MAX (DECODE (classid, 12, totalsendnum, 0)) totalsendnum2,
         MAX (DECODE (classid, 12, totalsendmoney, 0)) totalsendmoney2,
         MAX (DECODE (classid, 12, sendcustnum, 0)) sendcustnum2,
         MAX (DECODE (classid, 12, orderitem, 0)) orderitem2,
         MAX (DECODE (classid, 12, ordercount, 0)) ordercount2,
         MAX (DECODE (classid, 12, totalordernum, 0)) totalordernum2,
         MAX (DECODE (classid, 12, totalordermoney, 0)) totalordermoney2,
         MAX (DECODE (classid, 12, ordercustnum, 0)) ordercustnum2,
         MAX (DECODE (classid, 12, vscustnum, 0)) vscustnum2,
         MAX (DECODE (classid, 13, senditem, 0)) senditem3,
         MAX (DECODE (classid, 13, sendordercount, 0)) sendordercount3,
         MAX (DECODE (classid, 13, totalsendnum, 0)) totalsendnum3,
         MAX (DECODE (classid, 13, totalsendmoney, 0)) totalsendmoney3,
         MAX (DECODE (classid, 13, sendcustnum, 0)) sendcustnum3,
         MAX (DECODE (classid, 13, orderitem, 0)) orderitem3,
         MAX (DECODE (classid, 13, ordercount, 0)) ordercount3,
         MAX (DECODE (classid, 13, totalordernum, 0)) totalordernum3,
         MAX (DECODE (classid, 13, totalordermoney, 0)) totalordermoney3,
         MAX (DECODE (classid, 13, ordercustnum, 0)) ordercustnum3,
         MAX (DECODE (classid, 13, vscustnum, 0)) vscustnum3,
         MAX (DECODE (classid, 14, senditem, 0)) senditem4,
         MAX (DECODE (classid, 14, sendordercount, 0)) sendordercount4,
         MAX (DECODE (classid, 14, totalsendnum, 0)) totalsendnum4,
         MAX (DECODE (classid, 14, totalsendmoney, 0)) totalsendmoney4,
         MAX (DECODE (classid, 14, sendcustnum, 0)) sendcustnum4,
         MAX (DECODE (classid, 14, orderitem, 0)) orderitem4,
         MAX (DECODE (classid, 14, ordercount, 0)) ordercount4,
         MAX (DECODE (classid, 14, totalordernum, 0)) totalordernum4,
         MAX (DECODE (classid, 14, totalordermoney, 0)) totalordermoney4,
         MAX (DECODE (classid, 14, ordercustnum, 0)) ordercustnum4,
         MAX (DECODE (classid, 14, vscustnum, 0)) vscustnum4,
         MAX (DECODE (classid, 15, senditem, 0)) senditem5,
         MAX (DECODE (classid, 15, sendordercount, 0)) sendordercount5,
         MAX (DECODE (classid, 15, totalsendnum, 0)) totalsendnum5,
         MAX (DECODE (classid, 15, totalsendmoney, 0)) totalsendmoney5,
         MAX (DECODE (classid, 15, sendcustnum, 0)) sendcustnum5,
         MAX (DECODE (classid, 15, orderitem, 0)) orderitem5,
         MAX (DECODE (classid, 15, ordercount, 0)) ordercount5,
         MAX (DECODE (classid, 15, totalordernum, 0)) totalordernum5,
         MAX (DECODE (classid, 15, totalordermoney, 0)) totalordermoney5,
         MAX (DECODE (classid, 15, ordercustnum, 0)) ordercustnum5,
         MAX (DECODE (classid, 15, vscustnum, 0)) vscustnum5,
         MAX (DECODE (classid, 16, senditem, 0)) senditem6,
         MAX (DECODE (classid, 16, sendordercount, 0)) sendordercount6,
         MAX (DECODE (classid, 16, totalsendnum, 0)) totalsendnum6,
         MAX (DECODE (classid, 16, totalsendmoney, 0)) totalsendmoney6,
         MAX (DECODE (classid, 16, sendcustnum, 0)) sendcustnum6,
         MAX (DECODE (classid, 16, orderitem, 0)) orderitem6,
         MAX (DECODE (classid, 16, ordercount, 0)) ordercount6,
         MAX (DECODE (classid, 16, totalordernum, 0)) totalordernum6,
         MAX (DECODE (classid, 16, totalordermoney, 0)) totalordermoney6,
         MAX (DECODE (classid, 16, ordercustnum, 0)) ordercustnum6,
         MAX (DECODE (classid, 16, vscustnum, 0)) vscustnum6,
         MAX (DECODE (classid, 17, senditem, 0)) senditem7,
         MAX (DECODE (classid, 17, sendordercount, 0)) sendordercount7,
         MAX (DECODE (classid, 17, totalsendnum, 0)) totalsendnum7,
         MAX (DECODE (classid, 17, totalsendmoney, 0)) totalsendmoney7,
         MAX (DECODE (classid, 17, sendcustnum, 0)) sendcustnum7,
         MAX (DECODE (classid, 17, orderitem, 0)) orderitem7,
         MAX (DECODE (classid, 17, ordercount, 0)) ordercount7,
         MAX (DECODE (classid, 17, totalordernum, 0)) totalordernum7,
         MAX (DECODE (classid, 17, totalordermoney, 0)) totalordermoney7,
         MAX (DECODE (classid, 17, ordercustnum, 0)) ordercustnum7,
         MAX (DECODE (classid, 17, vscustnum, 0)) vscustnum7
...
from
...
group by ...
我的列比较多，哎~~~~ 如果用DECODE得些这么多。

你的功能是什么，为了导出数据还是页面输出，如果不需要一次性输出的话，那就分开来写好了，classid循环
先找出所有的classid，然后逐个取出，
select MAX (DECODE (classid, ?, senditem, 0)) senditem1,
  MAX (DECODE (classid, ?, sendordercount, 0)) sendordercount1,
  MAX (DECODE (classid, ?, totalsendnum, 0)) totalsendnum1,
  MAX (DECODE (classid, ?, totalsendmoney, 0)) totalsendmoney1,
  MAX (DECODE (classid, ?, sendcustnum, 0)) sendcustnum1,
  MAX (DECODE (classid, ?, orderitem, 0)) orderitem1,
  MAX (DECODE (classid, ?, ordercount, 0)) ordercount1,
  MAX (DECODE (classid, ?, totalordernum, 0)) totalordernum1,
  MAX (DECODE (classid, ?, totalordermoney, 0)) totalordermoney1,
  MAX (DECODE (classid, ?, ordercustnum, 0)) ordercustnum1,
  MAX (DECODE (classid, ?, vscustnum, 0)) vscustnum1;

调试易

请问如何把这纵向的数据转成横向呀？

解决方案 »