oracle如何行转列 oracle行转列列转行

oracle如何实现行转列

用union all

成都创新互联公司主营千阳网站建设的网络公司,主营网站建设方案,成都App定制开发,千阳h5微信小程序定制开发搭建,千阳网站营销推广欢迎千阳等地区企业咨询

假设列名分别为 col1 cola colb...

select col1,cola

from tabname

where ...

union all

select col1,colb

from tabname

where ...

union all

select col1,colc

from tabname

where ...

union all

select col1,cold

from tabname

where ...

union all

select col1,cole

from tabname

where ...

union all

select col1,colf

from tabname

where ...

oracle 行转列

测试表测了下，可以用，你看看，主要是先根据逗号进行分割，然后connect，level等于逗号的数目：

select id,nvl(substr(glbh,instr(glbh,',',1,lvl)+1,instr(glbh,',',1,lvl+1)-instr(glbh,',',1,lvl)-1),'kong') glbh

from (

select id,lvl,','||glbh||',' glbh

from test a ,

(select level lvl from dual connect by level=

(select max(length(glbh)-length(replace(glbh,',')))+1 from test)) b

) t1

where substr(glbh,instr(glbh,',',1,lvl)+1,instr(glbh,',',1,lvl+1)-instr(glbh,',',1,lvl)-1) is not null

order by id,glbh;

oracle 行转列 ，这个要怎么转

with  t(cate_displayname,info_waritername,count) as (

select '2014年','lizifeng',1 from dual 

union all

select '历史沿革','wenqingwen',20 from dual 

union all

select '领导讲话','lizifeng',3 from dual 

)

SELECT cate_displayname,

nvl(lizifeng,0) as lizifeng,

nvl(wenqingwen,0) as wenqingwen

FROM   t

PIVOT (

sum(count)        ---- pivot_clause

FOR info_waritername          ---- pivot_for_clause

IN  ('lizifeng' as lizifeng,'wenqingwen' as wenqingwen)   ---- pivot_in_clause

);

输出：

CATE_DISPLAYNAME           LIZIFENG WENQINGWEN

------------------------ ---------- ----------

2014年                            1          0

历史沿革                          0         20

领导讲话                          3          0