oracle 行列转换

oracle 行列转换　（http://blog.163.com/xiao_maomao_chong/blog/static/3168623920086445319803/）

笔试 2008-07-04 16:53:19 阅读118 评论0 字号：大中小

有一个字符串

,1,2,5,9,1,2,5,9,1,3,9,

通过SQL文将以上字符串处理后得到如下查询结果：

        col
        1
        2
        5
        9
        1
        2
        5
        9
        1
        3
        9

with tbl2 as (
select ',1,2,5,9,1,2,5,9,1,3,9,22,25,10,155555,'  as temp from dual
)
select substr(temp,currentIndex,nextIndex-currentIndex) as cols  from
(
    select temp
          ,level lv
          ,instr(temp,',',instr(temp,','),level)+1 as currentIndex
          ,instr(temp,',',instr(temp,','),level+1) as nextIndex from 
        (
        select temp
              ,length(temp)- length(replace(temp,','))-1 rowcnt
        from tbl2
        ) a
    connect by level <= rowcnt
)

挑战无处不在~~

with tbl2 as (
select ',1,2,5,9,1,2,5,9,1,3,9,'  as temp from dual
)
select substr(temp2,lv,1) from
(
    select temp2,level lv from 
        (
        select temp,replace(temp,',') temp2,length(replace(temp,',')) rowcnt from tbl2
        ) a
    connect by level <= rowcnt
)

select DECODE('A', '01' ,'WO JIAO DDDD', 'EEEE' , '02', '55' )
AS LIU from
(
select
 SUBSTR(mid,1,2) as a,
 SUBSTR(mid,4,2) as b,
 SUBSTR(mid,7,2) as c,
SUBSTR(mid,10,2) as d,
SUBSTR(mid,13,2) as e
 from liu )

**********************************************************************************

select DECODE(a, '01' ,b, '02' , c, d )
AS LIU from
(
select
 SUBSTR(mid,1,2) as a,
 SUBSTR(mid,4,2) as b,
 SUBSTR(mid,7,2) as c,
SUBSTR(mid,10,2) as d,
SUBSTR(mid,13,2) as e
 from liu )

 

 

with tbl2 as (
select wmsys.wm_concat(mid) as temp from liu
)
select substr(temp,currentIndex,nextIndex-currentIndex) as cols  from
(
    select temp
          ,level lv
          ,instr(temp,',',instr(temp,','),level)-2 as currentIndex
          ,instr(temp,',',instr(temp,','),level) as nextIndex from 
        (
        select temp
             ,length(replace(temp,','))/2 rowcnt
        from tbl2
        ) a
    connect by level <= rowcnt
)