Expanding row value into induvidual row

Question

I need some help with a SQL Query. I know this should be redesigned, but it is a small fix I am doing on a larger system :(.

The system has a table called sales, containing parts sold.

id  | date   | idpart
1   |unixtime|   227
2   |unixtime|   256 

And so on..

In the table Orderdetails, containing the content of the order, the parts are listed by id, and with the amount a unique part is ordered by a customer.

id |idpart | amount
1  | 255   | 4
2  | 265   | 2

Now, my problem is that I have to run a Query to populate the sales table but adding the idpart as a new row for the same amount of times the part has as amountvalue in order. I need result to be:

id  | date   | idpart
1   |unixtime|   227
2   |unixtime|   256 

3   |unixtime|   255
4   |unixtime|   255
5   |unixtime|   255
6   |unixtime|   255
7   |unixtime|   265
8   |unixtime|   265

Is there anyone who could give me some help on this problem?.

Answer 1

This is easy if you have a table with numbers. You can do it as:

select id, date, idpart
from sales
union all
select id, date, idpart
from orders o cross
     numbers n
     on n.number <= o.amount

This is assuming that date is coming from the orders table.

Now, you just need to generate the numbers table. This is a pain in MySQL. Perhaps you have another table you can use, such as a calendar table. Otherwise, you can create one by inserting into a table with an auto increment column.

In other databases, you can use the row_number() function to create such a table on the fly. But MySQL does not support this.