利用逗号拆分行，逗号拼接行

一、mysql 利用逗号拆分行，逗号拼接行

利用逗号拆分行

1.数据表

2.代码

SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( a.emp_id, ',', b.help_topic_id + 1 ), ',', - 1 ) AS emp_id,
emp_name,
chnl_id,
branch
FROM
xtc_emp_information a
JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.emp_id ) - LENGTH( REPLACE ( a.emp_id, ',', '' )) + 1 ) 
GROUP BY
branch,
chnl_id,
emp_name,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.emp_id, ',', b.help_topic_id + 1 ), ',', - 1 );

 3.运行结果

利用mysql 自带函数group_concat() 拼接行

1.测试表

2.代码

SELECT
	group_concat(emp_id),chnl_id,branch,
	emp_name
FROM
	test
GROUP BY
	chnl_id,branch,
	emp_name

3.运行结果

二、hivesql-根据逗号将一行数据拆成多行

一行拆多行

1、使用explode或posexplode方法

并不限制是逗号，其他分隔符都可以

1.1 对单列实行列转行 explode 配合 lateral view 使用

-- 测试数据
with temp as(select 1 as id ,'a,b,c' as name
               union 
             select 2 as id ,'d,e,f' as name)

测试数据如下：

select id,name,s_name
from temp 
lateral view explode(split(name,',')) t as s_name

结果如下：

1.2 对多列实行列转行 posexplode 配合 lateral view 使用

with temp as(select '1,2,3' as id ,'a,b,c' as name
               union 
             select '4,5,6' as id ,'d,e,f' as name)

测试数据如下：

-- 单个posexplode 返回index及值

select id,name,s_name_index,s_name
from temp 
lateral view posexplode(split(name,',')) t as s_name_index,s_name

结果如下：

-- 同时对两列都是用posexplode 

select id,name,s_id_index,s_id,s_name_index,s_name
from temp 
lateral view posexplode(split(id,',')) t as s_id_index,s_id
lateral view posexplode(split(name,',')) t as s_name_index,s_name

结果如下： 发现此时是全连接

-- 添加where限制

select id,name,s_id,s_name
from temp 
lateral view posexplode(split(id,',')) t as s_id_index,s_id
lateral view posexplode(split(name,',')) t as s_name_index,s_name
where s_id_index = s_name_index

结果如下：

如何将拆分的多行，合并成一行

2、hive可以使用collect_list搭配concat_ws实现。 

SELECT id, concat_ws(',', collect_set(name)) as names
FROM temp
GROUP BY id;

     

这里，concat_ws函数用于将合并的集合中的字符串用逗号分隔。collect_set函数收集所有的name值，并创建一个去重的集合。最后，使用GROUP BY按id进行分组，确保每个id的值合并成一行。

如果不需要去重，可以使用collect_list函数。

三、oracle 按逗号拆分多行

一行拆多行

测试数据

CREATE TABLE temp (
  id NUMBER(4) ,
  name varchar2(30) 
) ;

INSERT INTO temp VALUES ('1', 'a,b,c');
INSERT INTO temp VALUES ('2', 'd,e,f');

 

1、第一种写法

 select id,name,name_new from(
select substr(name,
              decode(level - 1, 0, 0, instr(name, ',', 1, level - 1)) + 1,
              (decode(level,
                      regexp_count(name, ',') + 1,
                      length(name) + 1,
                      instr(name, ',', 1, level))) -
              (decode(level - 1, 0, 0, instr(name, ',', 1, level - 1)) + 1)) as name_new,t.id,t.name
  from temp t
connect by level <= regexp_count(name, ',') + 1 ) a group by id,name,name_new order by id asc;

运行结果：

2、第二种写法

    select t1.id,t1.name, regexp_substr(t1.name, '[^,]+', 1, t2.lv) as name_new     --截取对应行数的数据
  from temp t1,
       (select level lv--生成行数序列数据 1到最大行数
          from (select max(regexp_count(a.name, '[^,]+', 1)) r_count--计算数据集中拆分后最大的行数
                  from temp a) b
        connect by level <= b.r_count) t2
 where regexp_substr(t1.name, '[^,]+', 1, t2.lv) is not null-- 排除掉空的数据
 order by 1;

运行结果： 

多行合并一行

测试数据

CREATE TABLE temp (
  id NUMBER(4) ,
  name varchar2(30) 
) ;

INSERT INTO temp VALUES ('1', 'a');
INSERT INTO temp VALUES ('1', 'b');
INSERT INTO temp VALUES ('1', 'c');
INSERT INTO temp VALUES ('2', 'd');
INSERT INTO temp VALUES ('2', 'e');
INSERT INTO temp VALUES ('2', 'f');

在Oracle中，可以使用LISTAGG函数将多行合并成一行。如果需要对结果进行分组，可以先使用GROUP BY子句对数据进行分组，然后再使用LISTAGG进行合并。

 代码实现：

SELECT id, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS name_new
FROM temp
GROUP BY id;

运行结果：

这个查询会按id进行分组，并将同一个id中的所有name合并成一个由逗号和空格分隔的列表。