• 前言
  有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值。如下表

一般有这两种常见需求(测试数据见文末)

1. 得到所有的不重复的值，如

SQL如下：

select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))
from
	(select group_concat(distinct `value`) as col from `row_to_col`) as a
join
	mysql.help_topic as b
on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

2. 显示每个值及其对应主键，如

SQL如下：

select a.pk,substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)
from
	(select `value` as col,pk from `row_to_col`) as a
join
	mysql.help_topic as b
on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

• 实现思路：
  需求1：
   1. 通过group_concat函数将value列的值拼接成一个逗号隔开的字符串，然后通过substring_index函数对字符串进行截取
   2. 通过substring_index函数特性，我们就需要知道字符串有多少个逗号，并且要告诉每个逗号的位置
   3. 逗号个数=char_length(字符串)-char_length(replace(字符串,’,’,’’))
   4. 逗号位置=mysql.help_topic.id < 逗号个数[+1]
   5. 最后通过distinct函数将截取后的单个值进行去重
  注意：
   1. mysql.help_topic表的自增id是从0开始，所以在进行截取时要对id进行+1。见: substring_index(a.col,’,’,b.help_topic_id+1)
   2. value列最后一个字符不是逗号时：逗号个数+1是为了截取时不漏掉最后一个逗号后的值，即: char_length(a.col) - char_length(replace(a.col,’,’,’’))+1；
    value列最后一个字符是逗号时：逗号个数就不需要+1了，直接：char_length(a.col) - char_length(replace(a.col,’,’,’’))
   3. 因为截取时id要+1，所以在连接时取的< ，而不是 <= 。见：b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,’,’,’’))[+1])
   4. mysql.help_topic(mysql version: 5.7.21-1)表的自增id，最大值为636。如果group_concat后的字符串中逗号个数大于该值，需要自己单独处理自增id的值

  需求2：思路基本与需求1同，只是最后的查询不一样

• 涉及到的函数：
  length:返回字符串所占的字节数，是计算字段的长度。一个汉字或是中文符号是算三个字符,一个数字或字母或英文符号算一个字符。
  char_length:返回字符串所占的字符数，不管汉字还是数字或者是字母或者符号(不分中英文)都算是一个字符。
  replace(str,old_string,new_string): 将字符串中str中所有的old_string替换成new_string。
  substring_index(被截取字段，关键字，关键字出现的次数)：截取字符串。如果关键字出现的次数是负数 ， 则是从后倒数，到字符串结束。
  group_concat([DISTINCT] 要连接的字段(可多个,逗号隔开) [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])：
  将group by产生的同一个分组中的值连接起来，返回一个字符串结果。分隔符，默认逗号。

• 测试数据：

DROP TABLE IF EXISTS `row_to_col`;
CREATE TABLE `row_to_col` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `row_to_col` VALUES ('1', 'ET,AT');
INSERT INTO `row_to_col` VALUES ('2', 'AT,BT');
INSERT INTO `row_to_col` VALUES ('3', 'AT,DT');
INSERT INTO `row_to_col` VALUES ('4', 'DT,CT,AT');

参考：
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727

一、 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