Repeat rows in the result based on an integer value in column

Question

I have following table.

Sales:
id      quantity    price_charged
------------------------------
101         2           100
102         3           300
103         1           120

I want to select the records such that it repeat Rows N time according to quantity column value.

So I need following results

id    quantity    price_charged
--------------------------------
101     1          50
101     1          50
102     1          100
102     1          100
102     1          100
103     1          120

What is the purpose of this ? You can utilize number-generator tables. — Madhur Bhaiya, Nov 21 '18 at 13:09

score 1 · Answer 1 · edited Nov 21 '18 at 13:23

I think, it is better to resolve not with query(SQL). There is some generation feature, but its performance is poor.

You have to change your model(store always 1 quantity), or process it in backend(java/c/stb.)

Select id, 
       1 as quantity, 
       price_charged 
from table_name t
JOIN 
(SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5) counter
ON (counter.n<=t.quantity)

The joined subquery reapeted numbers from 0 to 99999 it is the burn it maximum for quantity. The join repeat by the counter 0... quantity-1 values.

Please add explaination. also it is recommended to avoid using comma based joins and use modern `JOIN ..ON` syntax instead. +1 — Madhur Bhaiya, Nov 21 '18 at 13:14
I don't think this solution would work, and I am not sure whether this is a generic solution or just one off. I can think of a solution having stored procedure but I wanted to see if I can have a solution with select query. — Vik, Nov 21 '18 at 13:22
Perhaps a little bit better, if you insert counts into one table and join to that table. But it works if 99999>=quantity>=0. — László Tóth, Nov 21 '18 at 13:25

score 1 · Answer 2 · answered Nov 23 '18 at 06:16

I was able to come up with a solution for my problem after referring an answer for how to generate series in mysql. Here is the link.

SELECT
  sal.id,
  1 as quantity, sal.quantity as originalQty,
  sal.price_charged/sal.quantity
FROM
  (SELECT
     @num := @num + 1 AS count
   FROM
     sales, -- this can be any table but it should have row count 
            -- more than what we expect the max value of Sales.quantity column
     (SELECT @num := 0) num
   LIMIT
     100) ctr
  JOIN sales sal
    ON sal.quantity >= ctr.count
    order by id;

GMB · Answer 3 · 2021-09-17T17:09:13.773

If you are lucky enough to be running MySQL 8.0, you can use a recursive CTE to solve this problem. This is an elegant solution that does not require creating a list of numbers of using variables.

Consider this query:

WITH RECURSIVE cte AS (
      SELECT 1 n, id, quantity, price_charged FROM sales
      UNION ALL
      SELECT n + 1, id, quantity, price_charged FROM cte WHERE n < quantity
)
SELECT id, quantity, price_charged/quantity quantity
FROM cte
ORDER BY id;

In this DB Fiddle with your sample data, the query returns:

| id  | quantity | quantity |
| --- | -------- | -------- |
| 101 | 2        | 50       |
| 101 | 2        | 50       |
| 102 | 3        | 100      |
| 102 | 3        | 100      |
| 102 | 3        | 100      |
| 103 | 1        | 120      |

Repeat rows in the result based on an integer value in column

3 Answers3

Linked

Related