My company's client has a table to store inventory data, this inventory separates products using a column called product_code, and it has another column called Qtd that stores how many of the same products are in stock. What I need help with is that I need to separate this one row of products into n different rows, with n being the value stored in Qtd. Example:
Original:
| row | product_code | Qtd |
|---|---|---|
| 1 | 111 | 15 |
New:
| row | product_code | Qtd |
|---|---|---|
| 1 | 111 | 1 |
| 2 | 111 | 1 |
| 3 | 111 | 1 |
| ... | ... | ... |
| 14 | 111 | 1 |
| 15 | 111 | 1 |
I need to create this new rows because they're trying to introduce a new column that represents the serial number of the products, that is unique and I'm unable to use joins because this serial number is stored in a different database. We have a way to assign a serial number to a product based on its product code, but only the first serial number is being paired correctly, and separating this single row into different rows would solve this particular problem in an practical way, if it's possible of course.
We've tried doing this using our software, but it ended with only the first serial number being assigned to a row and all the others being left unused. We're hoping that this try would separate the data into different rows.
Ideally you do this on the entire table, while nothing else is changing it. If you need to support concurrent activity, it is a lot more complicated.
Start by preparing making a new table to get the new rows:
create table new_inventory like inventory;
rename table inventory to old_inventory, new_inventory to inventory;
Then insert the split rows using a recursive cte to generate them:
insert into inventory (product_code, Qtd)
with recursive generate_rows as (
select product_code, Qtd from old_inventory
union all
select product_code, Qtd-1 from generate_rows where Qtd > 1
)
select product_code, 1 Qtd from generate_rows;