Return records repeated according to field value

Question

I have a table like this

Name         Qty   
-----        -----
BD             2
SD             1
XE             3

I need to return this table records repeated according to the field Qty value. for example first row would be repeated twice with all same values.

I was thinking to use nested FOR Select inside stored procedure with return parameters

For Select name, qty from mytable into :name, :qty do
  begin
    if (qty > 1 ) then begin
      i = 1; -- should I start from 2 ?
      while (i <= :qty) do
      begin

        for select name, qty from mytable into :name1, :qty1 do ...

        SUSPEND;

        i = i + 1;
      end


    end

    SUSPEND;
end

Can this stored procedure return the correct result or should I use another way ?

I use FireBird 2.5 and please discard any typos in the previous SQL I am looking only to main idea.

Answer 1

You can do it using recursive CTE, supported since version 2.1, something like

WITH RECURSIVE tQty AS(
    SELECT ta.name, ta.qty
       FROM T ta
       WHERE(ta.qty > 0)
 UNION ALL
    SELECT tb.name, tb.qty-1
       FROM tQty tb
       WHERE(tb.qty-1 > 0)
 )
 SELECT name,qty FROM tQty
       ORDER BY name