locked
Generate Sequence Number From The Given Number in SQL RRS feed

  • Question

  • User2101416554 posted
    CREATE TABLE [dbo].[SizeData1](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[SizeCode] [int] NULL,
    	[Size] [varchar](10) NULL,
    	[SizeNumber] [varchar](15) NULL,
     CONSTRAINT [PK_SizeData1] 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
    Table Data ....
    
    ID	Size	Qty
    1	S1	0
    2	S2	10
    3	S3	5
    4	S4	4
    5	S5	3
    6	S6	2
    7	S7	1
    8	S8	10
    9	S9	9

    I need the sequence like below 

    ID	Size	Qty	Total
    1	S1	0	0
    2	S2	10	1,2,3,4,5,6,7,8,9,10,11
    3	S3	5	1,2,3,4,5
    4	S4	4	1,2,3,4
    5	S5	3	1,2,3
    6	S6	2	1,2
    7	S7	1	1
    8	S8	10	1,2,3,4,5,6,7,8,9,10
    9	S9	9	1,2,3,4,5,6,7,8,9
    

    Wednesday, January 1, 2020 12:00 PM

Answers

  • User-719153870 posted

    Hi umerfaiz123,

    Not sure if there's any built-in function in SQL that can meet the requirement directly. Below is the thought i can provide which uses the custom function in SQL:

    Notice: the table structure and table data provided don't match so i created a new table #Size and assume the Total record for ID=2 is incorrect( ',11' is unnecessary) in the expected query result provided.

    create table #Size(ID int identity(1,1),Size varchar(15),Qty int) 
    insert into #Size values('S1',0),('S2',10),('S3',5),('S4',4),('S5',3),('S6',2),('S7',1),('S8',10),('S9',9)
    select * from #Size
    
    --create the custom function 'GetTotal'
    IF OBJECT_ID (N'dbo.GetTotal', N'FN') IS NOT NULL  
        DROP FUNCTION GetTotal;  
    GO  
    CREATE FUNCTION dbo.GetTotal(@Qty int)  
    RETURNS varchar(max)   
    AS   
    BEGIN  
        DECLARE @Total varchar(max);  
        declare @i int=1;
        if @Qty=0
        set @Total='0,';
        else
        set @Total='';
        while(@i<=@Qty)
        begin
        set @Total+=CONVERT(varchar(max),@i)+','
        set @i+=1;
        end
        set @Total=SUBSTRING(@Total,1,LEN(@Total)-1);
        RETURN @Total;  
    END; 
    
    go
    --Use the function dbo.GetTotal like below
    select *,dbo.GetTotal(Qty) as Total from #Size

    Below is the result of this demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 2, 2020 2:11 AM

All replies

  • User-719153870 posted

    Hi umerfaiz123,

    Not sure if there's any built-in function in SQL that can meet the requirement directly. Below is the thought i can provide which uses the custom function in SQL:

    Notice: the table structure and table data provided don't match so i created a new table #Size and assume the Total record for ID=2 is incorrect( ',11' is unnecessary) in the expected query result provided.

    create table #Size(ID int identity(1,1),Size varchar(15),Qty int) 
    insert into #Size values('S1',0),('S2',10),('S3',5),('S4',4),('S5',3),('S6',2),('S7',1),('S8',10),('S9',9)
    select * from #Size
    
    --create the custom function 'GetTotal'
    IF OBJECT_ID (N'dbo.GetTotal', N'FN') IS NOT NULL  
        DROP FUNCTION GetTotal;  
    GO  
    CREATE FUNCTION dbo.GetTotal(@Qty int)  
    RETURNS varchar(max)   
    AS   
    BEGIN  
        DECLARE @Total varchar(max);  
        declare @i int=1;
        if @Qty=0
        set @Total='0,';
        else
        set @Total='';
        while(@i<=@Qty)
        begin
        set @Total+=CONVERT(varchar(max),@i)+','
        set @i+=1;
        end
        set @Total=SUBSTRING(@Total,1,LEN(@Total)-1);
        RETURN @Total;  
    END; 
    
    go
    --Use the function dbo.GetTotal like below
    select *,dbo.GetTotal(Qty) as Total from #Size

    Below is the result of this demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 2, 2020 2:11 AM
  • User77042963 posted
    ---SQL Server 2017, 2019
    
    create table #Size(ID int identity(1,1),Size varchar(15),Qty int) 
    insert into #Size values('S1',0),('S2',10),('S3',5),('S4',4),('S5',3),('S6',2),('S7',1),('S8',10),('S9',9)
    select ID,Size,Qty, String_agg(n,',') total from #Size
    outer apply (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d(n)
     Where Qty>=n 
     Group by  ID,Size,Qty
     union all
     select *,Cast(0 as nvarchar(100)) total from #Size
     Where Qty=0
     Order by ID
    
    drop  table #Size

    Thursday, January 2, 2020 4:13 PM