日期:2014-05-17  浏览次数:20826 次

请问如何把这纵向的数据转成横向呀?
有表TBL字段id,classid,a,b,c,如何根据classid转成下面的数据
原来:


期望:


请帮忙啦,我这里只列举了一行数据。

------解决方案--------------------
看不到图,贴个昨天写的比较蛋疼的SQL给你参考
SQL code
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

------解决方案--------------------
SQL code

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