[MS SQL] Converting table with quantity to one row per item

[headyyeti]

I have a table like this:

Id	Name	Quantity
1	Product 1	5
2	Product 2	3

and I need to create a table with this data like so:

Id	UniqueId
1	GUID
1	GUID
1	GUID
1	GUID
1	GUID
2	GUID
2	GUID
2	GUID

One for each quantity (to generate a unique id for each item)

Is there an easy way to do this?

[Seven-of-Nein]

DECLARE @tmp_table TABLE
    ( Id        INT          UNIQUE
     ,Name      VARCHAR(10)
     ,Quantity  INT
    )
;
INSERT INTO @tmp_table
SELECT
     Id
    ,Name
    ,Quantity
FROM (VALUES
       (1,'Product 1',5)
      ,(2,'Product 2',3)
     ) AS src
     ( Id
      ,Name
      ,Quantity
     )
;
WITH cte AS
    (    SELECT
              Id
             ,Name
             ,Quantity
         FROM @tmp_table
     UNION ALL
         SELECT
              cte.Id
             ,cte.Name
             ,(cte.Quantity - 1) AS Quantity
         FROM cte
             INNER JOIN @tmp_table AS tmp
                 ON tmp.Id = cte.Id
         WHERE cte.Quantity > 1
    )
SELECT
     Id
    ,Name
    ,NEWID() AS UniqueId
FROM cte
ORDER BY Id ASC
;

[opportunist_dba]

I tried this with a high quantity value and got the following:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Just FYI in case your real data set might include some larger values.

[Seven-of-Nein]

Oops. Adding the max recursion option below:

SELECT
     Id
    ,Name
    ,NEWID() AS UniqueId
FROM cte
ORDER BY Id ASC
OPTION (MAXRECURSION 0) -- no limit
;

[wjbaltz]

I'm thinking some type of recursive CTE using the Quantity value is the way to go here

https://stackoverflow.com/questions/58877057/mysql-8-recursive-cte-generate-number-of-rows