demo how to reverse mysql group_concat() function

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;

table1

— we want to transform it to

table


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;

table2

— 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);

table3

— 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;

table4

— 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 ;

table5

— 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;

table

<END>

 

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.