locked
In SQL Sequence Number RRS feed

  • Question

  • User2101416554 posted

    This is the thread ...

    https://forums.asp.net/t/2162845.aspx?Generate+Sequence+Number+From+The+Given+Number+in+SQL

    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

    The above query is working fine ...

    I need to generate the sequence number in 3 digit like 001,002,003,004,005,006,007,008,009,010,011 ...........099,100,101,102 etc ...

    The above query generate as 1,2,3,4,5,6,7,8,9,10,11,.....................99,100,101,102

    Friday, January 3, 2020 4:35 AM

Answers

  • User665608656 posted

    Hi umerfaiz123,

    According to your description, you can change your sql as following sql statements:

    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),(select replicate('0',3-LEN(@i)) + CONVERT(VARCHAR,@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

    Here is the result :

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 3, 2020 7:40 AM
  • User77042963 posted
    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)
    
    ;with mycte as (
    select ID,Size,Qty,Cast(n as nvarchar(100)) n from #Size
    outer apply (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d(n)
     Where Qty>=n 
    )
    
    SELECT t1.ID,t1.Size,t1.Qty,
           Stuff(( SELECT ',' + Right('00'+ t2.n ,3)
               FROM mycte t2
              WHERE t2.ID = t1.ID and  t2.Size = t1.Size
              ORDER BY id
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
      FROM mycte t1
     GROUP BY  t1.ID,t1.Size,t1.Qty
     union all
     select *,Cast(0 as nvarchar(100)) total from #Size
     Where Qty=0
     Order by id
     
     
    
    drop  table #Size

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 3, 2020 8:47 PM

All replies

  • User665608656 posted

    Hi umerfaiz123,

    According to your description, you can change your sql as following sql statements:

    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),(select replicate('0',3-LEN(@i)) + CONVERT(VARCHAR,@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

    Here is the result :

    Best Regards,

    YongQing.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 3, 2020 7:40 AM
  • User77042963 posted
    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)
    
    ;with mycte as (
    select ID,Size,Qty,Cast(n as nvarchar(100)) n from #Size
    outer apply (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d(n)
     Where Qty>=n 
    )
    
    SELECT t1.ID,t1.Size,t1.Qty,
           Stuff(( SELECT ',' + Right('00'+ t2.n ,3)
               FROM mycte t2
              WHERE t2.ID = t1.ID and  t2.Size = t1.Size
              ORDER BY id
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
      FROM mycte t1
     GROUP BY  t1.ID,t1.Size,t1.Qty
     union all
     select *,Cast(0 as nvarchar(100)) total from #Size
     Where Qty=0
     Order by id
     
     
    
    drop  table #Size

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 3, 2020 8:47 PM