Generate Sequence Number From The Given Number in SQL

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

Answer 1

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

Answer 2

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