Generate a serial number based on quantity column in sql-CodePudding

Hi Experts I have a table like this

Order_no	Qty
1	3
2	5
3	1
4	3

I need to generate a column 'serial no' having values based on 'qty'

Output needed

OrderNo	Qty	SerailNo
1	3	1
1	3	2
1	3	3
2	5	1
2	5	2
2	5	3
2	5	4
2	5	5
3	1	1
4	3	1
4	3	2
4	3	3

Any suggestions?

Thanks in advance!!

CodePudding user response：

You don't mention the specific database so I'll assume you are using PostgreSQL, aren't you?

You can use a Recursive CTE to expand the rows. For example:

with recursive
n as (
  select order_no, qty, 1 as serial_no from t1
 union all
  select order_no, qty, serial_no   1
  from n
  where serial_no < qty
)
select * from n order by order_no, serial_no

Result:

 order_no  qty  serial_no 
 --------- ---- --------- 
 1         3    1         
 1         3    2         
 1         3    3         
 2         5    1         
 2         5    2         
 2         5    3         
 2         5    4         
 2         5    5         
 3         1    1         
 4         3    1         
 4         3    2         
 4         3    3

See running example at DB Fiddle.

EDIT FOR ORACLE

If you are using Oracle the query changes a bit to:

with
n (order_no, qty, serial_no) as (
  select order_no, qty, 1 from t1
 union all
  select order_no, qty, serial_no   1
  from n
  where serial_no < qty
)
select * from n order by order_no, serial_no

Result:

 ORDER_NO  QTY  SERIAL_NO 
 --------- ---- --------- 
 1         3    1         
 1         3    2         
 1         3    3         
 2         5    1         
 2         5    2         
 2         5    3         
 2         5    4         
 2         5    5         
 3         1    1         
 4         3    1         
 4         3    2         
 4         3    3

See running example at db<>fiddle.

CodePudding user response：

You should first provide the database you're using. Whether it's oracle, Sql Server, PostGreSQL will determine which procedural language to use. It's very likely that you'll need to do this in two steps: 1st: Duplicate the number of rows based on the column Qty using a decreasing loop 2nd: You'll need to create a sequential partionned column based on the Qty column