Show Resultset Row Based On Number Of Arrival Quantity [Answered]RSS

• • Reply
  Gopi.MCA

  Member

  142 Points

  360 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

  ItemName	Color	Arrival Quantity
  Apple	Red	4
  Mango	Green	2
  Banana	Yellow	1

  The Resultset has to repeat row based on total arrival quantity

  ItemName	Color
  Apple	Red
  Apple	Red
  Apple	Red
  Apple	Red
  Mango	Green
  Mango	Green
  Banana	Yellow

• • Reply
  limno

  All-Star

  122875 Points

  9904 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 mytable

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

  Contributor

  6081 Points

  1443 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

  SELECT *
  FROM DataTable
  CROSS APPLY dbo.NumbersTable(1,RepeatCount,1)

  T-SQL programming SQL Server Tutorials
  SQL Server 2017