CREATE TABLE mytable(
ItemName VARCHAR(6) NOT NULL
,Color VARCHAR(6) NOT NULL
,Arrival_Quantity INTEGER NOT NULL
);
INSERT INTO mytable(ItemName,Color,Arrival_Quantity) VALUES
('Apple','Red',4)
,('Mango','Green',2)
,('Banana','Yellow',1);
Select ItemName, Color
From mytable
Cross apply (values(1),(2),(3),(4),(5),(6)/*,(7),(8)....*/) d(n)
WHERE Arrival_Quantity>=n
drop table mytable
I found a solution for this requirement using a numbers table
Using the numbers table, again with CROSS APPLY as suggested in previous reply, database developer can
repeat rows by a given number within the source row
SELECT *
FROM DataTable
CROSS APPLY dbo.NumbersTable(1,RepeatCount,1)
Member
294 Points
679 Posts
Show Resultset Row Based On Number Of Arrival Quantity
Dec 06, 2018 05:33 PM|Gopi.MCA|LINK
Hello
This is my table data
The Resultset has to repeat row based on total arrival quantity
All-Star
123252 Points
10024 Posts
Moderator
Re: Show Resultset Row Based On Number Of Arrival Quantity
Dec 06, 2018 07:30 PM|limno|LINK
CREATE TABLE mytable( ItemName VARCHAR(6) NOT NULL ,Color VARCHAR(6) NOT NULL ,Arrival_Quantity INTEGER NOT NULL ); INSERT INTO mytable(ItemName,Color,Arrival_Quantity) VALUES ('Apple','Red',4) ,('Mango','Green',2) ,('Banana','Yellow',1); Select ItemName, Color From mytable Cross apply (values(1),(2),(3),(4),(5),(6)/*,(7),(8)....*/) d(n) WHERE Arrival_Quantity>=n drop table mytableFormat your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Contributor
6101 Points
1449 Posts
Re: Show Resultset Row Based On Number Of Arrival Quantity
Dec 07, 2018 06:11 AM|eralper|LINK
I found a solution for this requirement using a numbers table
Using the numbers table, again with CROSS APPLY as suggested in previous reply, database developer can repeat rows by a given number within the source row
SQL Server 2017