myslq行转列

原数据

-- tablename1  default.test
name                score
zhangsan;lisi        80
zhaoyun ;            60
qiansan;             70
wangliu; sunwu       50

tablename2

SQL

select 
substring_index(
		substring_index(
			a.name,
			';',
			b.help_topic_id + 1
		),
		';' ,- 1
	) AS name,

count(id) as total 

from 
default.test as a

JOIN mysql.help_topic b 

ON b.help_topic_id < (
	length(a.name) - length(
		REPLACE (a.name, ';', '')
	) + 1
) 


where created  between '%startday%' and '%endday%'  and name!= ''
group by 1
order by total desc

limit 1,30

处理后数据

-- tablename1  default.test
name               score
zhangsan            80
lisi                80
qiansan             70
zhaoyun             60
wangliu             50
sunwu               50