Generating Rows Based on Column Value in Mysql

Question

I want to insert rows base on cell value in Column QuantityTable in Producttemp table and insert into table ProductionActual For example, the first table is shown below:

Table Producttemp

+-------------+----------+
| Requisition | Quantity |
+-------------+----------+
| GJ002_01_AD |    5     |
| GJ002_02_AD |    3     |
+-------------+----------+

and I would like the output to be as follows:

Table Productactual

+-------------+----------+----------+
| Requisition | Quantity |  Series  |
+-------------+----------+----------+
| GJ002_01_AD |     5    |     1    |
| GJ002_01_AD |     5    |     2    |
| GJ002_01_AD |     5    |     3    |
| GJ002_01_AD |     5    |     4    |
| GJ002_01_AD |     5    |     5    |
| GJ002_02_AD |     3    |     1    |
| GJ002_02_AD |     3    |     2    |
| GJ002_02_AD |     3    |     3    |

I found similar question below for sql server, but it's not working in MySQL database

Generating Rows Based on Column Value

There are 3 answers in the published link - which of them did you try that didn't work? also add the mysql version you are on.. — P.Salmon
– P.Salmon, Commented Dec 12, 2022 at 8:50

NewLFC · Accepted Answer · 2022-12-15 15:43:22Z

1

Try this, this will create new rows with running numbers in series

`

WITH RECURSIVE 
Factor (Requisition, Quantity, Series, FactorKey) AS
(
    SELECT Requisition, Quantity,1,CONCAT(Requisition, Quantity,) FROM Producttemp
    UNION ALL
    SELECT 
        Requisition, 
        Quantity,
        IF(FactorKey != CONCAT(Requisition, Quantity),2,Series+1) AS Series ,
        CONCAT(Requisition, Quantity)
    FROM 
        Quantity WHERE Series<Quantity 
    
)
SELECT * FROM Factor ORDER BY Requisition, Quantity;

`

answered Dec 15, 2022 at 15:43

NewLFC

924 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thavisack VRCHDV Over a year ago

the recursive query will disappear after refreshing the table. is there a way to put them into another table after your query? or any suggestions?

NewLFC Over a year ago

I think you can create a stored procedure, declare a cursor that reads the source table, and for each row inserts qty rows into the destination table.

Collectives™ on Stack Overflow

Generating Rows Based on Column Value in Mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related