In SQL Sequence Number[Answered] RSS

• • Reply
  umerfaiz123@...

  Member

  12 Points

  33 Posts

  In SQL Sequence Number

  Jan 03, 2020 04:35 AM|umerfaiz123@outlook.com|LINK

  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

• • Reply
  Yongqing Yu

  Contributor

  3690 Points

  1043 Posts

  Re: In SQL Sequence Number

  Jan 03, 2020 07:40 AM|Yongqing Yu|LINK

  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.

  MSDN Community Support
  
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• • Reply
  limno

  All-Star

  123240 Points

  10019 Posts

  Moderator

  Re: In SQL Sequence Number

  Jan 03, 2020 08:47 PM|limno|LINK

  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

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm