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
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.
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
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
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
Contributor
3720 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:
Here is the result :
Best Regards,
YongQing.
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.
All-Star
123252 Points
10024 Posts
Moderator
Re: In SQL Sequence Number
Jan 03, 2020 08:47 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm