0

I have a table having store,combo products sold, quantity sold and sales. This is different from “Reverse GROUP_CONCAT” in MySQL?" question in terms of sales not getting attributed to every product but only to the primary product. For eg. A combo pack of LED TV and sound System has to be broken into LED and Sound system however the sales would be mapped to LED as it is the primary product.

INSERT INTO table(store, product, qty, sales)
VALUES('Store 1', 'Prod l + Prod f', '2', '1000'),
('Store 1', 'Prod a + Prod b + Prod c', '1', '15000'),
('Store 2', 'Prod h + Prod a + Prod y + Prod q', '1', '20000');

I would like to delimit the product column into single products and allocate the sales and quantity to the primary product. I would like the output table to be something like these. I tried this function "regexp_split_to_table" but was unable to do it.

INSERT INTO table(store, product, qty, sales)
VALUES('Store 1', 'Prod l', '2', '1000'),
('Store 1', 'Prod f', '', ''),
('Store 1', 'Prod a', '1', '15000'),
('Store 1', 'Prod b', '', ''),
('Store 1', 'Prod c', '', ''),
('Store 2', 'Prod h + Prod a + Prod y + Prod q', '1', '20000'),
('Store 2', 'Prod a', '', ''),
('Store 2', 'Prod y', '', ''),
('Store 2', 'Prod q', '', '');

Can anyone help me with it please?

user2025936
  • 11
  • 3
  • 1
    Are you using MySQL or MS SQL Server? (Don't tag products not involved.) – jarlh Oct 06 '17 at 14:14
  • Regardless of which DBMS you are using the output is quite strange. You have both the original data AND portions of that data split across multiple rows. Once you decide which database you are actually using you are going to need to explain the output more clearly. – Sean Lange Oct 06 '17 at 14:18
  • I am using Mysql. I will then use the output table to be joined with item master to get each product's categories. this report will further be used to manage inventory. I need to get the total category quantity and sales number. Based on this ordering would be done. – user2025936 Oct 06 '17 at 14:19
  • Here is a great place to start. https://stackoverflow.com/help/mcve – Sean Lange Oct 06 '17 at 14:21
  • Should I add a picture of the desired output table? – user2025936 Oct 06 '17 at 14:22
  • Possible duplicate of ["Reverse GROUP\_CONCAT" in MySQL?](https://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql) – Ravi Oct 06 '17 at 14:23

0 Answers0