locked
Generate number from 1 to given number in SQL RRS feed

  • 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

    Thursday, October 3, 2019 11:43 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 3, 2019 12:38 PM
  • 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]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 3, 2019 1:39 PM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 3, 2019 12:38 PM
  • 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]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 3, 2019 1:39 PM