Generate number from 1 to given number in SQL

Question

User1910487977 posted

How to number from given number?

Say for eg i have the value number in my table. 10,15, 25,35.

CREATE TABLE [dbo].[TestOrder](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OrderQty] [int] NULL,
 CONSTRAINT [PK_TestOrder] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ID	OrderQty
1	10
2	15
3	30

I would like to generate number for 1 to the order qty.

For the above eg:

ID   OrderQty

1     10              1 2 3 4 5 6 7 8 9 10

2      15             1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

3       30            1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

Answer 1

User452040443 posted

Try:

with CTE_Rec as
(
    select ID, OrderQty, 1 as GenerateNumber
    from TestOrder
    
    union all
    
    select ID, OrderQty, GenerateNumber + 1
    from CTE_Rec
    where GenerateNumber < OrderQty
)

select * from CTE_Rec
order by ID, GenerateNumber

Hope this help

Answer 2

User77042963 posted

You need a number table  to work with.

 Here is an example:

CREATE TABLE [dbo].[TestOrder](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OrderQty] [int] NULL)

	Insert into [TestOrder] values(10),(15),(30)

Select ID,[OrderQty], Number+1 as [OrderQty2]  from [TestOrder]
cross apply (SELECT [number]       
  FROM [master].[dbo].[spt_values]
  where type='p' ) d(number)
  WHERE number<[OrderQty]


 drop table [TestOrder]