This is a quick note and demo about method to reverse mysql group_concat()function, with customed seperataor ‘-‘, i.e. split values from cell and transpose into rows.
It is also a good example of how ‘cross join’ method helps to perform a recursive
operation without the need of creating stored procedure but simply in a select
statement
Here is the sample data for illustrating the use case:
drop temporary table if exists raw_table;
create TEMPORARY table if not exists raw_table (ID INT(11), code char(100));
insert into raw_table
values (1,‘A’),(2,‘B’),(3,‘C-D’),(4,‘E-F-G’);
select * from raw_table;
— we want to transform it to
Solution
— 1st use ‘substring_index()’ to split string
select ID, code,
substring_index(substring_index(code,‘-‘,1),‘-‘,–1) subcode1,
— take RMB from
RMB-MN-A
substring_index(substring_index(code,‘-‘,2),‘-‘,–1) subcode2,
— take MN from
RMB-MN-A then take MN
substring_index(substring_index(code,‘-‘,3),‘-‘,–1) subcode3
— take A from
RMB-MN-A then take MN
from raw_table;
— we can spot the pattern substring_index(substring_index(code,‘-‘,x),‘-‘,–1) where x is number of separator found in string plus 1
— 2nd we need solution to perform recursive operation to transform
the results
— use cross join to perform do … loop in select statement
drop temporary table if exists sequence;
create temporary table sequence (seq INT(1));
insert into sequence
values (0),(1),(2),(3),(4),(5);
— note that here I just create sequential numbers up to 5. It is not a good pratise at all. A better way is to query the max number of separator then generate the sequential number up to the max number of occurence.
— However, mysql doesn’t support generating sequential number table. There are many hacks out there, since it is not the purpose of this article, so I just take the easy way.
— Here is the result how ‘cross join’ helps to transpose the result
SELECT
ID,
SUBSTRING_INDEX(
SUBSTRING_INDEX(code, ‘-‘, seq),
‘-‘ ,– 1) sub_code,
seq
FROM
sequence
CROSS JOIN raw_table
ORDER By ID, seq;
— we want to remove redundant result
— define variable for occurrence of seperator ‘-‘
select ID, code, 1+length(code)–length(replace(code,‘-‘,“)) occurence
from raw_table ;
— Here we have the final query
SELECT
ID,
SUBSTRING_INDEX(
SUBSTRING_INDEX(code, ‘-‘, seq),
‘-‘ ,– 1) sub_code,
seq
FROM
sequence
CROSS JOIN raw_table
WHERE
seq BETWEEN 1
AND (SELECT
1 + LENGTH(code) – LENGTH(REPLACE(code, ‘-‘, “)))
ORDER By ID, seq;
<END>
閒時讀閒書 