0

How can I split columns "MyCode" and "MyAmount" into multiple rows in MySQL 8.0.33

I tried it with only 1 column, but with 2 columns I can't get it right

MyID MyCode MyAmount
1209 MR;CA,KK 3.95;100.15;-9.75
1210 CA;GU 13.75;1004.60

The expected result:

MyID MyCode MyAmount
1209 MR 3.95
1209 CA 100.15
1209 KK -9.75
1210 CA 13.75
1210 GU 1004.60
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
chris
  • 1
  • 1
  • What have you tried so far? – lemon May 17 '23 at 17:38
  • 1
    You could try what has been answered in this [question](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows). – Igor F May 17 '23 at 17:40
  • i tried it with only 1 column, but with 2 or more columns i can't get it right – chris May 18 '23 at 04:12
  • If you can't query your data, that may be a sign that you should store it in a different way. In this case, as rows of a second table, with one value in each column per row, not as semicolumn-separated strings of values. This is a one-to-many relationship. – Bill Karwin May 18 '23 at 15:24

0 Answers0