Q:

SQL For Each - write to file

So we have a Production Table with the following data (in simple terms)
ID, Item, QTY
1,AAA,3
2,BBB,4

so 2 production tasks, one for a quantity of 3, and one with a quantity of 4. I require an export file (txt) that would display the following
ID,Item
1,AAA
1,AAA
1,AAA
2,BBB
2,BBB
2,BBB
2,BBB

Basically, I need a file with a line for each of the quantity. This is because I use a 3rd party software that uses each line in the file to create a ticket/label for the task.
any help on the above would be gratefully appreciated.
Thanks,
Dean

A:

Basically, you need a numbers table, so you can do:
select p.id, p.item
from production p join
     numbers n
     on n.n <= p.qty;

If your table has enough rows, then one ANSI-standard method that will work in many databases is:
select p.id, p.item
from production p join
     (select row_number() over (order by p.id) as n
      from production
     ) n
     on n.n <= p.qty;

There are other database-specific ways of generating numbers.
Another ANSI compatible method is recursive CTEs:
with cte (id, item) as (
      select id, item, qty
      from production 
      union all
      select id, item, qty - 1
      from production
      where qty > 0
     )
select id, item
from cte;

(Note:  sometimes the recursive keyword is needed.)

