0

I have a MySQL table that has some values that are separated by comma on multiple columns. So the idea is to create new rows with values from the same position in relation to the comma for each original row.

Original query result:

#   Col1        Col2        Col3                Col4 
1   id1         10          0.1                 0.2
2   id2,id3     5,4         0.2,0.3             0.3,0.5
3   id4         3,8         0.5,0.0             1.0,0.8
4   id5         3,8,9,12    0.5,0.0,0.5,0.0     1.0,0.8,0.0,0.5

I want to generate new rows for the table, by spliting the original rows that have values separated by comma. If there are more than one value on col1, the new rows would get the values from Col2, Col3 and Col4 following the same position. If there is only one value for Col1, the value would be repeated and the new rows would get the respective values from the Cols 2, 3 and 4. 

#   Col1        Col2        Col3        Col4 
1   id1         10          0.1         0.2
2   id2         5           0.2         0.3
3   id3         4           0.3         0.5
4   id4         3           0.5         1.0
5   id4         8           0.0         0.8
6   id5         3           0.5         1.0
7   id5         8           0.0         0.8
8   id5         9           0.5         0.0
9   id5         12          0.0         0.5

I know it would be much easier to do it using Python, but my database is larger than 20gb, so I wish I could do it using MySQL commands. I appreciate the help!!

Gabriel Goncalves
  • 29
  • 3
  • https://stackoverflow.com/questions/46890617/restructuring-a-bad-database-with-php-loops-or-mysql/46892143#46892143 mine answer contains a MySQL only method to split CVS data.. in the question the separator was ; you need off course need to change that... bad would to be indeed that normalized your source table so they don't contain anny CSV data. CSV data in MySQL always requires writting challenging SQL. – Raymond Nijland Feb 20 '18 at 21:01
  • You could conceivably write a procedure to do it, but frankly, i'd use Python. :P – cHao Feb 20 '18 at 21:01
  • [This post may help you](https://www.periscopedata.com/blog/splitting-comma-separated-values-in-mysql) – Chris Feb 20 '18 at 21:35
  • You could use a stored procedure with a loop; each iteration would (1)create new rows from the first values of any row with commas using an INSERT...SELECT, and then (2) remove the first values from those source rows with their following comma using UPDATE. The loop would terminate when now new rows are created. The loop would make use of the INSTR and SUBSTR methods. – Uueerdo Feb 20 '18 at 22:09
  • Um, don't have values separated by commas – Strawberry Feb 20 '18 at 23:41

0 Answers0