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, Dec 12 '22 at 08:50
Does this answer your question? [CROSS/OUTER APPLY in MySQL](https://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql) — P.Salmon, Dec 12 '22 at 08:53

score 1 · Accepted Answer · answered Dec 15 '22 at 15:43

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 '22 at 15:43

NewLFC

92
3

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? – Thavisack VRCHDV Dec 15 '22 at 15:54
1

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. – NewLFC Dec 15 '22 at 15:59

Generating Rows Based on Column Value in Mysql

1 Answers1