0

I have a column called 'Order' and each product inside the column is separated by commas. Is it possible to pull each string from the single cell to its prospected variables?

Example: Column name: Orders Values inside a single cell: H001, H002, H003

Orders
H001, H002, H003

Expected result

H1      H2      H3
H001    H002    H003

Below is the snipping of the table,

https://snipboard.io/06tkAl.jpg

Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
John Hang
  • 125
  • 7
  • can you brief the scenerio behind this logic.May be there will another way to achieve this – Shaniawan Jul 13 '20 at 07:10
  • the reason behind this is that each product has a price and I'm planning to add a column call total. I can then use the price to calculate the total. This table is called the packaging and I have another table that has the products individually with it's own price. – John Hang Jul 13 '20 at 07:13
  • Do one work.Create a seperate table name it "Products_packages".For every package and product create a seperate row in this table.Suppose you will create two foreign keys.One wil lbe package_id and other will be product_id in this new table and then add a join of this table to Products table .This way you can get the price of every Product – Shaniawan Jul 13 '20 at 07:20
  • 1
    Please use below query `select SUBSTRING_INDEX(orders, ',', 1) as H1, SUBSTRING_INDEX(SUBSTRING_INDEX(orders, ',', 2), ',', -1) as H2, SUBSTRING_INDEX(orders, ',', -1) as H3 from table_name;` Demo - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f16ff96d5f61758425fbfbb1a930fab8 – Jim Macaulay Jul 13 '20 at 07:23
  • If `H001, H002, H003` are order numbers (invoice etc.), that means it will grow to a maximum `H999`.. So are you planning to have 900+ columns? (e.g. H1, H2, H3 ... H997, H998, H999) – FanoFN Jul 13 '20 at 07:37
  • tcadidot0 - It's only there, but shouldn't reach 20. Maybe max 10. Jim - I'll test that. Thanks – John Hang Jul 13 '20 at 07:50
  • You should change your db design and leave that query alone. It would be so much easier. – Shadow Jul 13 '20 at 08:18

0 Answers0