【Question】
I need to create a SQL query for the following situation: I have a product, quantity, and a field called packaging. I need my query to return the product and su the quantity repeatedly until the quantity in the "packaging". I thought about using the command "While", but do not know how. Thank you.

The product is well in the products table:
cod_product, quantity, packing
123  , 40, 2
I need to create a query that shows this product as follows:
Sequential, product, quantity.
1, 123, 20
2, 123, 20
I need the result to be shown the way to print labels, so I need a label to each package with its respective weight.

A solution:

WITH
 Pass0 as (select 1 as C union all select 1), --2 rows
 Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
 Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
 Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
 Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
 Tally as (select row_number() over(order by C) as SEQUENCIAL from Pass4)
select
T.SEQUENCIAL
, P.IDPRD as COD_PRODUCT
, P.QUANTIDADE / p.QTDEVOLUMEUNITARIO as QTD
, P.IDMOV
from TITMMOV P (NOLOCK)
inner join tally T (NOLOCK)
on P.QTDEVOLUMEUNITARIO >= T.SEQUENCIAL
where P.IDMOV = 2431
ORDER BY P.IDPRD , T.SEQUENCIAL

【Answer】

Your question is to split one row into multiple rows by a certain rule. That amounts to the inverse operation of group by and aggregate operation. SQL takes a roundabout route to express the algorithm, generating difficult code. If the size of your source data isn’t large, try using the following SPL (Structured Process Language) script. It’s simple and clear:

A1: Retrieve data from products table with a SQL-like statement.

A2: Create a new table sequence comprised of sequential, product, quantity, and split each of A1’s record into N records (N is the packing value) to insert into the new table.

esProc bridges between an application and the database to feed a prepared result set to the higher-level Java program or reporting to through its JDBC interface. The call of a SPL script is similar to that of a database result set. Details are explained in How to Call an SPL Script in Java.