SQL - Insert rows based on count

Question

I'm looking to insert a number of repeating rows into a table, based upon the value set by another - ideas & suggestions would be greatly appreciated.

tblType
Type     Qty
Apple    2
Banana   1
Mango    3

tblResult
Apple
Apple
Banana
Mango
Mango
Mango

Answer 1

You can use recursive CTE to build the data:

WITH rcte AS (
    SELECT Type, Qty, 1 AS n
    FROM tbltype
    WHERE Qty > 0

    UNION ALL

    SELECT Type, Qty, n + 1
    FROM rcte
    WHERE n < Qty
)

-- INSERT INTO tblresult
SELECT Type
FROM rcte

Answer 2

You can use recursive cte :

with cte as (
   select t.type, t.qty, 1 as start
   from table t
   union all
   select c.type, c.qty, start + 1 
   from cte c
   where start < c.qty
)

insert into table (type)
     select c.type
     from cte c
     order by c.type
     option (maxrecusrion 0);

Answer 3

You may try the recursive CTE for this

DECLARE @T TABLE
(
    SeqNo INT IDENTITY(1,1),
    [Type] VARCHAR(20),
    Qty INT
)

INSERT INTO @T
VALUES('Apple',2),('Banana',1),('Mango',3)

;WITH CTE
AS
(
    SELECT
        [Type],
        Qty
        FROM @T
    UNION ALL
    SELECT
        [Type],
        Qty = Qty -1
        FROM CTE
            WHERE Qty>1
)
SELECT
    *
    FROM CTE
    ORDER BY [Type]

Result

Type                 Qty
-------------------- -----------
Apple                2
Apple                1
Banana               1
Mango                3
Mango                2
Mango                1

Answer 4

You can try the cte and ROW_NUMBER() to generate rows based on another column values as shown below.

create table tblType([Type] varchar(20), Qty int)
insert into tblType values
('Apple',    2),
('Banana',   1),
('Mango',   3)

;WITH
  expanded
AS
(
  SELECT [Type], Qty FROM tblType    
  UNION ALL    
  SELECT [Type], Qty - 1 FROM expanded WHERE Qty > 1
)

SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY [Type], Qty) AS unique_ref
FROM
  expanded
ORDER BY
  [Type],
  Qty

The output is as shown below:

Type    Qty    unique_ref
------------------------
Apple    1     1
Apple    2     2
Banana   1     3
Mango    1     4
Mango    2     5
Mango    3     6

Answer 5

If you don't mind using the system table master..[spt_values] and your Qty can't exceed 2537 in that case you can use following query.

It will be faster compared to recursive CTE.

SELECT t1.type 
FROM   tbltype t1 
       INNER JOIN (SELECT ( Row_number() 
                              OVER (ORDER BY (SELECT NULL)) ) RN 
                   FROM   master..[spt_values] T1) t2 
               ON t1.qty >= t2.rn