行转列sql整理
测试表三个字段:月份,类型,数据
方法一:
select t."月份",
sum(case when t."类型"='蒸发量' then t."数据" else 0 end) as "蒸发量",
sum(case when t."类型"='降水量' then t."数据" else 0 end) as "降水量",
sum(case when t."类型"='温度' then t."数据" else 0 end) as "温度"
from CESHIBIAO t
group by t."月份"
方法二:(与上面一样只是用了不同的函数)
--decode(行专列,将多行的值改为多列显示)(decode的那个column,是某个列的值,也就是将某个列的值作为新的列的column,MAX聚集函数也可以用sum、min、avg等其他聚集函数替代)
select t."月份",
sum("DECODE"(t."类型",'蒸发量',t."数据",0)) as "蒸发量",
sum("DECODE"(t."类型",'降水量',t."数据",0)) as "降水量",
sum("DECODE"(t."类型",'温度',t."数据",0)) as "温度"
from CESHIBIAO t
group by t."月份"
方法三:pivot orcale11g以后
--pivot(行专列,将多行的值改为多列显示)(for in的那个column,是某个列的值,也就是将某个列的值作为新的列的column,这个column下边的值好像只能来自一列)
select * from (
select "月份","数据","类型" from ADMIN_CESHIBIAO)
pivot(
sum("数据") for "类型" in ('蒸发量','降水量','温度')
)
列转行
WMSYS.WM_CONCAT: 依赖WMSYS 用户,不同oracle环境时可能用不了,返回类型为CLOB,可用substr截取长度后to_char转化为字符类型
LISTAGG : 11g2才提供的函数,不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型,最大长度为4000.
方法一:
wm_concat(合并列名)
select wm_concat("月份") name from ADMIN_CESHIBIAO;
方法二:把逗号替换“|”
select replace(wm_concat("月份"),',','|') name from ADMIN_CESHIBIAO;
方法三:listagg
--listagg(合并多行的值为字符串,只用一列来显示)
有over形式的
partition :分隔的意思
select distinct t."月份", listagg(t."类型", '|') within group(order by t."数据") over (partition by t."月份") as "dd"
from ADMIN_CESHIBIAO t
where t."月份" = '一月'
group by形式的
select t."月份", listagg(t."类型", '|') within group(order by t."数据") as "dd"
from ADMIN_CESHIBIAO t
where t."月份" = '一月'
group by t."月份"