mysql列转行 行转列

SELECT 
     flag
    ,substring_index(substring_index(t.context,‘,‘, b.help_topic_id + 1), ‘,‘, -1) as result
FROM (
    select ‘aa‘ as flag,‘1,2,3,4,5,6,7‘ as context union all
    select ‘bb‘ as flag,‘1,2,3,4,5,6‘ as context union all
    select ‘cc‘ as flag,‘1,2,3,4,5‘ as context union all
    select ‘dd‘ as flag,‘1,2,3,4‘ as context union all
    select ‘ee‘ as flag,‘1,2,3‘ as context 
) t 
join (
    select 0 as help_topic_id union all
    select 1 as help_topic_id union all
    select 2 as help_topic_id union all
    select 3 as help_topic_id union all
    select 4 as help_topic_id union all
    select 5 as help_topic_id union all
    select 6 as help_topic_id union all
    select 7 as help_topic_id union all
    select 8 as help_topic_id union all
    select 9 as help_topic_id
) b 
    ON b.help_topic_id < (LENGTH(t.context) - LENGTH(REPLACE(t.context, ‘,‘, ‘‘)) + 1)
order by flag,result
;

select
     name
    ,group_concat(age) as ages
from (
    select ‘a‘ as name, 0 as age union all
    select ‘b‘ as name, 1 as age union all
    select ‘b‘ as name, 2 as age union all
    select ‘c‘ as name, 3 as age union all
    select ‘c‘ as name, 4 as age union all
    select ‘c‘ as name, 5 as age union all
    select ‘d‘ as name, 6 as age union all
    select ‘d‘ as name, 7 as age union all
    select ‘d‘ as name, 8 as age union all
    select ‘d‘ as name, 9 as age
) b 
group by name
;