oracle和mysql中列转行用逗号分隔函数

一、 Oracle

oracle11g以前旧版本语法：wmsys.wm_concat(列名)

select wmsys.wm_concat(username) FROM students;-- 将username列记录值用逗号连接起来

以其它列分组，role_name列打印成一行用逗号隔开：

select u.user_id, u.dept_id, u.login_name, u.user_name, wmsys.wm_concat(r.role_name) as role_name from sys_user u
		left join sys_user_role ur on ur.user_id = u.user_id
		left join sys_role r on r.role_id = ur.role_id
		where u.del_flag = '0'
		group by u.user_id, u.dept_id, u.login_name, u.user_name
		order by u.create_time desc

oracle11g以上版本不支持wmsys.wm_concat，使用listagg() WITHIN GROUP ()函数代替：

select u.user_id, u.dept_id, u.login_name, u.user_name, listagg(r.role_name,',') within group(order by r.role_name) as role_name from sys_user u
		left join sys_user_role ur on ur.user_id = u.user_id
		left join sys_role r on r.role_id = ur.role_id
		where u.del_flag = '0'
		group by u.user_id, u.dept_id, u.login_name, u.user_name
		order by u.create_time desc

二、Mysql中group_concat函数

完整的语法：

group_concat([DISTINCT] 要连接的字段 [Order BY  排序字段 ASC/DESC] [Separator '分隔符'])

1. 以其它列分组，role_name列打印成一行用逗号隔开(默认逗号分隔)：

select user_id,group_concat(role_name) as role_name from sys_user group by user_id;

±-----±-------------------+
| user_id|role_name |
±-----±-------------------+
|1 | 安全员,施工人员,安全员|
|2 | 项目经理,现场监理 |
|3 | 总监|
±-----±-------------------+

2. role_name列打印成一行，用分号分隔

select user_id,group_concat(role_name separator ';') as role_name from sys_user group by user_id; 

±-----±-------------------+
| user_id|role_name |
±-----±-------------------+
|1 | 安全员;施工人员,安全员|
|2 | 项目经理;现场监理 |
|3 | 总监|
±-----±-------------------+

3. role_name列打印成一行，去重后用逗号隔开

select user_id,group_concat(distinct  role_name) as role_name from sys_user group by user_id; 

±-----±-------------------+
| user_id|role_name |
±-----±-------------------+
|1 | 安全员,施工人员|
|2 | 项目经理,现场监理 |
|3 | 总监|
±-----±-------------------+

4. ole_name列打印成一行，逗号分隔，倒序排列

select user_id,group_concat(role_name order by role_name desc) as role_name from sys_user group by user_id; 

±-----±-------------------+
| user_id|role_name |
±-----±-------------------+
|1 | 施工人员,安全员|
|2 | 现场监理,项目经理 |
|3 | 总监|
±-----±-------------------+

参考博文：https://www.cnblogs.com/yingmo/p/6148360.html