CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
R. Salehi
R. Salehi

Reputation: 183

Repeat Rows N Times According to Column Value, Without Limit in Repeating

I have a table like this in SQL Server 2014:

 IDSupply    Qty    PartName
 ---------------------------
 1          2        C
 2          4        B
 3          50000    A

I want to repeat each row N times according to Qty column with Index (for example for C from 1 to 4 as index)

And what is the problem: I used 2 queries for this goal but they are repeat just 100 times, like this:

WITH tally AS 
(
    SELECT 1 n
    UNION ALL
    SELECT n + 1 
    FROM tally 
)
SELECT partname, n.n Position
FROM supplylist t 
JOIN tally n ON n.n <= t.qty
ORDER BY partname, Position

and other way works for repeating each row 32000 times but I can't use it as a CTE (because of the OPTION(MAXRECURSION 32500) problem with CTE)

 WITH Numbers(Num) AS
 (
     SELECT 1 AS Num

     UNION ALL 

     SELECT Num + 1
     FROM Numbers c
     WHERE c.Num < 30000
 )
 SELECT partname, qty, num
 FROM supplylist
 JOIN Numbers ON supplylist.qty >= Numbers.Num
 ORDER BY partname, num
 OPTION(MAXRECURSION 32500)

Notice: I can't use above code in CTE structure like following:

 WITH Numbers(Num) AS
 (
     SELECT 1 AS Num

     UNION ALL 

     SELECT Num + 1
     FROM Numbers c
     WHERE c.Num < 30000
 ),
 CTE as
 (
     SELECT partname,qty, num
     FROM supplylist
     JOIN Numbers ON supplylist.qty >= Numbers.Num
     ORDER BY partname, num
     OPTION(MAXRECURSION 32500)
 )
 SELECT * 
 FROM CTE

Please help me to do that without limit and without problem with CTE structure.

Upvotes: 0

Views: 3158

Answers (2)

R. Salehi
R. Salehi

Reputation: 183

Finlay I found the solution. We can not use "OPTION(MAXRECURSION 0)" in CTE structure but we can use our query as a function and use "OPTION(MAXRECURSION 0)" in calling and running Function likes following:

Create fnCreateIndex
(  
  @Pr1 Int
)
RETURNS TABLE 
AS
RETURN 
(
WITH Numbers(Num) AS
(
  SELECT 1 AS Num
  UNION ALL 
  SELECT Num + 1
  FROM Numbers c
  WHERE c.Num < @Pr1), 
CTE as
(
 SELECT partname, qty, num
 FROM supplylist
 JOIN Numbers ON supplylist.qty >= Numbers.Num
)
 Select * from cte
)

Finaly we can use this for getting the resuls:

 select * from fnCreateIndex (50000)  order by partname, num OPTION(MAXRECURSION 0)

I found solution according to: https://stackoverflow.com/a/7428903/4885037

Upvotes: 0

Jim Jimson
Jim Jimson

Reputation: 2518

You can set the max recursion to whatever you want. Try the below:

DECLARE @supplylist TABLE
(
IDSupply INTEGER,
Qty INTEGER,
PartName CHAR(1)
);

INSERT INTO @supplylist VALUES (1,2,'C'),(2,4,'B'),(3,50000,'C');

WITH Numbers(Num) AS
 (
   SELECT 1 AS Num
   UNION ALL 
   SELECT Num + 1
   FROM   Numbers c
   WHERE c.Num < 100000
 )


SELECT partname,qty, num
  FROM   @supplylist s
  INNER JOIN Numbers
        ON  Numbers.Num <= s.qty
  ORDER BY partname, num
  OPTION(MAXRECURSION 0);

Upvotes: 3

Related Questions