【Question】

I have a table storing various items. It has a quantity field containing numeric values that are greater than 1. Below is part of the Item table:
code  item  quantity
C1-23  tester  3
C3-11  microscope  2
I need to split each row into multiple rows according to the quantity value to generate a new table. Is there any fast way to do this? Thanks.
Expected result:
code  item  quantity
C1-23  tester  1
C1-23  tester  1
C1-23  tester  1
C3-11  microscope  1
C3-11  microscope  1

【Answer】

Different databases have their own methods to do this. In MSSQL, you can join Item table with master..spt_values to get it done:

select t.code,item,1 quantity from Item t

join master..spt_values s on s.type = 'P' and quantiy > s.number

If you want a more universal method, you can code it in SPL. The Structured Process Language offers loop functions to generate simple and easy to understand script:

A1: Retrieve data from Item table with a SQL-style statement.

A2: “quantiy*[~]” copies each row N times (N is the quantity value) to get a sequence of rows; conj() function concatenates rows from every sequence; and run() function re-assigns 1 to each of quantity values.