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
新闻名称：oracle如何行转列 oracle行转列列转行
文章源于：http://scyanting.com/article/hpgihh.html
