MySql 查询将包含定界符的列转换为行

Question

基本上我需要编写一个查询来选择下表

UID | Col A | Col B
123   x;y;z    v;b

转换为以下要求格式。

123 , Col A, X
123 , Col A, y
123 , Col A, z
123 , Col B, v
123 , Col B, b

非常感谢任何建议。

Answer 1

这很痛苦，但这里有一个方法:

select uid, 'Col A' as col,
       substring_index(substring_index(colA, ';', n.n), ';', -1) as val,
from t cross join
     (select 1 as n union all select 2 union all select 3
     ) n
     on (length(replace(colA, ';', '; ')) - length(colA)) <= n.n + 1
union all
select uid, 'Col B' as col,
       substring_index(substring_index(colB, ';', n.n), ';', -1) as val,
from t cross join
     (select 1 as n union all select 2 union all select 3
     ) n
     on (length(replace(colB, ';', '; ')) - length(colB)) <= n.n + 1;