How to merge multiple values in a column with other column which also have multiple values sperated by comma

Question

column1 | column2 | Result
32,33   | A,B     | A32,A33,B32,B33

I have given an example above In which I have two columns with multiple values separated by a comma and I want to merge the first two columns and want to get a result as shown in the Result column.

This is a job that is far better suited to your application framework than to MySQL — Nick, Dec 26 '18 at 08:02
but i just want to change in mysql after that I will delete first two columns. — rAnA bilAl, Dec 26 '18 at 08:08
Actually I don't need these values in separated columns I want to merge their values and save in one column.i want to do that using mysql query otherwise I would have to do lots of data entry. — rAnA bilAl, Dec 26 '18 at 08:13
The problem is that the solution requires looping through a split of the values in each column and MySQL isn't very good at either of those things. — Nick, Dec 26 '18 at 08:15
If your at changing your schema, do it properly and get rid of the comma separated lists. Use a table's row for each value. (https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — sticky bit, Dec 26 '18 at 08:23
I want in this format i don't need to make a separate table for that lists. — rAnA bilAl, Dec 26 '18 at 09:07

score 0 · Answer 1 · answered Aug 24 '19 at 21:36

On MySQL 8.0, You can first split the columns on the basis of comma separated values -

 with recursive col1 as (select 1 id, '32,33' as column1
                         union all
                         select 2, '34,35'),
      rec_col1 as (select id, column1 as remain, substring_index( column1, ',', 1 ) as column1
                   from col1
                   union all
                   select id, substring( remain, char_length( column1 )+2 ),substring_index( substring( remain, char_length( column1 ) +2  ), ',', 1 )
                   from rec_col1
                   where char_length( remain ) > char_length( column1 )

RESULT

id  column1
1   32
1   33
2   34
2   35

Similarly, Second column -

with recursive col2 as (select 1 id, 'A,B' as column2
                        union all
                        select 2, 'C,D'),
     rec_col2 as (select id, column2 as remain, substring_index( column2, ',', 1 ) as column2
                  from col2
                  union all
                  select id, substring( remain, char_length( column2 )+2 ),substring_index( substring( remain, char_length( column2 ) +2  ), ',', 1 )
                  from rec_col2
                  where char_length( remain ) > char_length( column2 ))
select id, column2 from rec_col2
order by id;

RESULT

id  column2
1   A
1   B
2   C
2   D

After splitting both the columns, You can join them on the basis of id -

select concat(c2.column2, c1.column1) result_rows
from rec_col1 c1
join rec_col2 c2 on c1.id = c2.id

RESULT

result_rows
A32
B32
C34
D34
A33
B33
C35
D35

Finally you can use GROUP_CONCAT to combine them -

select group_concat(c2.column2, c1.column1 order by c2.column2, c1.column1) result
from rec_col1 c1
join rec_col2 c2 on c1.id = c2.id 
group by c2.id

RESULT

result
A32,A33,B32,B33
C34,C35,D34,D35

How to merge multiple values in a column with other column which also have multiple values sperated by comma

1 Answers1