字段逗号分割 查询出来的sql是多列如何实现

Question

数据库是这样的：

查询结果想要这样的：

不想用程序去控制，因为不确定这个分割字段的长度，产生的列数也不同，所以想用sql控制，sql也是动态的，而且后续查询要分页展示，列数也不确定暂时没有想到什么办法，看各位有什么好的办法，谢谢

Answer 1

create table t 
(
    id int ,
    con VARCHAR(100)
);


insert into T values(1,'1,2,3,4,5,6');
insert into T values(2,'9,8');

 
set @sql='select id,';
set @sql=CONCAT(@sql,(select GROUP_CONCAT('max(if(help_topic_id=',help_topic_id,',V,null)) as 字段',help_topic_id+1) from  mysql.help_topic where help_topic_id <(
select max(LENGTH(con)-LENGTH(replace(con,',',''))) from t )));
set @sql=CONCAT(@sql,'
from 
( 
 SELECT t.id,help_topic_id,substring_index(substring_index(con,'','', help_topic_id + 1), '','', -1)  as v
 FROM   mysql.help_topic,T
 where help_topic_id < (LENGTH(con) - LENGTH(REPLACE(con, '','', '''')) + 1)
) a
group by id ');
 
 
PREPARE stmt FROM @sql;       
EXECUTE stmt ;        
deallocate prepare stmt;