oracle列转行拼成一个字符串,oracle行转列/列转行/字符串拆分 测试

--列转行

--示例1

select * from (

select o.sname, count(1) dataNum

from t_Olm_Onlinemondata d

join t_Aaa_ou o on o.ou_id = d.company_id

where d.mon_time > to_date(‘2019-11-01 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)

group by o.sname

) pivot( sum(dataNum) for sname in(‘石家庄炼化‘,‘燕山石化‘,‘安庆石化‘,‘天津石化‘,‘河南油田‘,‘湖北化肥‘,‘扬子石化‘,‘金陵石化‘,‘茂名石化‘ ))

--示例2

create table demo(id int,name varchar(20),nums int);  ---- 创建表

insert into demo values(1, ‘苹果‘, 1000);

insert into demo values(2, ‘苹果‘, 2000);

insert into demo values(3, ‘苹果‘, 4000);

insert into demo values(4, ‘橘子‘, 5000);

insert into demo values(5, ‘橘子‘, 3000);

insert into demo values(6, ‘葡萄‘, 3500);

insert into demo values(7, ‘芒果‘, 4200);

insert into demo values(8, ‘芒果‘, 5500);

select *

from (select name, sum(nums) nums from demo t group by t.name)

pivot(sum(nums)

for name in(‘苹果‘, ‘橘子‘, ‘葡萄‘, ‘芒果‘))

--行转列

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);

insert into Fruit values(1,‘苹果‘,1000,2000,3300,5000);

insert into Fruit values(2,‘橘子‘,3000,3000,3200,1500);

insert into Fruit values(3,‘香蕉‘,2500,3500,2200,2500);

insert into Fruit values(4,‘葡萄‘,1500,2500,1200,3500);

select * from Fruit;

select * from Fruit unpivot(

xiaoshou --行转列  列别名 新列名

for jidu --行转列  给转换的列起的 列别名

in(Q1, Q2, Q3, Q4)

)

--下面的语句执行错误 行转列 pivot和列转行unpivot的in只能使用静态字符串(http://www.oracle-developer.net/display.php?id=506)

with b as(

select listagg(o.sname,‘,‘) within group(order by o.ou_id) sname from T_AAA_ou o where o.parent_id is null and o.enabled =1 and o.type=1

)

select * from (

select o.sname, count(1) dataNum

from t_Olm_Onlinemondata d

join t_Aaa_ou o on o.ou_id = d.company_id

where d.mon_time > to_date(‘2019-11-01 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)

group by o.sname

) pivot( sum(dataNum) for sname in( b  ));

--字符串拼接 listagg within group (order by o.ou_id)

select listagg(o.sname, ‘,‘) within group(order by o.ou_id) sname

from T_AAA_ou o

where 1=1

and o.parent_id is null

and o.enabled = 1

and o.type = 1

group by o.parent_id;

--字符串拆分

drop table Fruit;

create table Fruit(id int,name varchar(50), Q1 int, Q2 int, Q3 int, Q4 int);

insert into Fruit values(1,‘苹果,香瓜,哈密瓜,梨‘,1000,2000,3300,5000);

insert into Fruit values(2,‘橘子,葡萄,荔枝,榴莲‘,3000,3000,3200,1500);

insert into Fruit values(3,‘香蕉,雪莲果,山竹,樱桃‘,2500,3500,2200,2500);

insert into Fruit values(4,‘葡萄,芒果,沙棘,无花果‘,1500,2500,1200,3500);

select* from Fruit

select

(select max( regexp_substr(Fruit.Name,‘[^,]+‘,1,level,‘i‘)) from dual connect by level <= length(Fruit.Name)-length(regexp_replace(‘,‘,‘‘)))names

from Fruit

select t.name from Fruit t inner join a on a.name = t.name;

--获取字符串长度

select LENGTH(‘17,20,23‘) from dual;

--获取没有逗号的字符串长度

select LENGTH(REGEXP_REPLACE(‘17,20,23‘, ‘,‘, ‘‘)) from dual;

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;

SELECT NVL(REGEXP_SUBSTR(‘17,20,23‘, ‘[^,]+‘, 1, LEVEL, ‘i‘), ‘NULLL‘) AS STR

FROM DUAL

CONNECT BY LEVEL<= LENGTH(‘17,20,23‘) - LENGTH(REGEXP_REPLACE(‘17,20,23‘, ‘,‘, ‘‘))+1;

-- 字符串拆分  参考网址 http://blog.sina.com.cn/s/blog_679e928c0102wij3.html

--listagg(字段,‘,‘) within group(order by 分组字段)

oracle行转列/列转行/字符串拆分 测试

标签：val   取字符串   varchar   inner   字符串拆分   sel   ace   isp   agg

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/liuqiang18/p/13025404.html