oracle行转列经典实现 Oracle列转行,行转列?
Oracle列转行,行转列?
oracle下可以用函数decode处理:
select 产品名称,
sum(decode(季度,"第一季度",销售额,0)) 第一季度销售额,
sum(decode(季度,"第二季度",销售额,0)) 第二季度销售额,
sum(decode(季度,"第三季度",销售额,0)) 第三季度销售额,
sum(decode(季度,"第四季度",销售额,0)) 第四季度销售额,
from 表名
group by 产品名称
oracle中怎么把行转变成列,列转变成行?
select to_char(wm_concat(name)) from tablename使用wm_concat函数将列转化成行,使用逗号分割。最后转化成字符串
oracle查询行转列怎么写?
推荐用WM_CONCAT函数 SELECT A.STD, WM_CONCAT(A.F3) NEW_RESUL FROM (SELECT STD, F3 FROM (SELECT STD, F1 F3 FROM TABLE_A UNION ALL SELECT STD, F2 F3 FROM TABLE_A) A) A GROUP BY A.STD
oracle列名不确定,行转列?
select decode(t.rn,1,t.单位名称) as 单位名称1 ,
decode(t.rn,2,t.单位名称) as 单位名称2,
decode(t.rn,3,t.单位名称) as 单位名称3,
from (
select c.单位名称,to_number() over(partition by c.单位名称) as rn
from table1 a,table2 b,table3 c
where a.id1 = b.id1
and b.id2=c.id2
) t
这是在知道多少行记录数的情况下这样实现,如果不知道多少行,则最好用个中间表,搞个语句块,把相关的数先放到中间表在进行处理。
oracle动态的行转列怎么搞?
用union all
假设列名分别为 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实现行转换成列的方法?
数据表示例:假设要将name值作为行,course值作为列,转换后效果为:对应的SQL如下:方法1:使用表连接SELECT DISTINCT a.name,(SELECT score FROM grade b WHEREa.name=b.name AND b.course="语文") AS "语文",(SELECT score FROM grade b WHEREa.name=b.name AND b.course="数学") AS "数学",(SELECT score FROM grade b WHEREa.name=b.name AND b.course="英语") AS "英语"FROM grade a方法2:使用分组SELECT name,SUM(CASE course WHEN "语文" THEN score END) AS "语文",SUM(CASE course WHEN "数学" THEN score END) AS "数学",SUM(CASE course WHEN "英语" THEN score END) AS "英语"FROM grade GROUP BY name
oracle行转列经典实现 oracle列转换成行 oracle行转列解决一对多
版权声明:本文内容由互联网用户自发贡献,本站不承担相关法律责任.如有侵权/违法内容,本站将立刻删除。