Show multiple copies of row based on contents or "Inverse of GROUP BY"

Question

I have a table in MySQL that contains products ordered in an online store. I would like to generate a list of every ordered item, so I need to return multiple copies of a row based on the quantity of the item ordered:

+------------------+-------------+------------+-------+----------+
| order_product_id | order_id    | product_id | model | quantity |
+------------------+-------------+------------+-------+----------+
|             2254 |        1427 |         56 | B-WO  |        2 |
|             2253 |        1426 |         56 | B-WO  |        2 |
|             2252 |        1425 |         56 | B-WO  |        1 |
|             2251 |        1425 |         51 | 11    |        1 |
+------------------+-------------+------------+-------+----------+

Desired output, optionally including a counter for the item number out of the total:

+------------------+-------------+------------+-------+----------+------------------------+
| order_product_id | order_id    | product_id | model | quantity | item number (optional) |
+------------------+-------------+------------+-------+----------+------------------------+
|             2254 |        1427 |         56 | B-WO  |        2 |                      1 |
|             2254 |        1427 |         56 | B-WO  |        2 |                      2 |
|             2253 |        1426 |         56 | B-WO  |        2 |                      1 |
|             2253 |        1426 |         56 | B-WO  |        2 |                      2 |
|             2252 |        1425 |         56 | B-WO  |        1 |                      1 |
|             2251 |        1425 |         51 | 11    |        1 |                      1 |
+------------------+-------------+------------+-------+----------+------------------------+

Is there a straightforward MySQL way to do this?

score 1 · Accepted Answer · answered Oct 06 '19 at 04:12

If you are running MySQL 8.0+ (or MariaDB 10.2.2+), you can do this with recursive Common Table Expressions (CTE). It should look something like this:

WITH RECURSIVE cte AS (
    SELECT order_product_id, order_id, product_id, model, quantity
    FROM view_order_items

    UNION ALL

    SELECT cte.order_product_id, cte.order_id, cte.product_id, cte.model, cte.quantity - 1
    FROM cte INNER JOIN view_order_items
        ON cte.order_product_id = view_order_items.order_product_id
    WHERE cte.quentity > 1
)
SELECT * FROM cte

If you are running an older version, upgrade it.

Show multiple copies of row based on contents or "Inverse of GROUP BY"

1 Answers1