t-sql return multiple rows depending on field value

Question

i am trying to run an export on a system that only allows t-sql. i know enough of php to make a foreach loop, but i don't know enough of t-sql to generate multiple rows for a given quantity. i need a result to make a list of items with "1 of 4" like data included in the result

given a table like

orderid, product, quantity
1000,ball,3
1001,bike,4
1002,hat,2

how do i get a select query result like:

orderid, item_num, total_items, product

1000,1,3,ball

1000,2,3,ball

1000,3,3,ball

1001,1,4,bike

1001,2,4,bike

1001,3,4,bike

1001,4,4,bike

1002,1,2,hat

1002,2,2,hat

Answer 1

You can do this with the aid of an auxiliary numbers table.

;WITH T(orderid, product, quantity) AS
(
select 1000,'ball',3 union all
select 1001,'bike',4 union all
select 1002,'hat',2
)

SELECT orderid, number as item_num, quantity as total_items, product
FROM T
JOIN master..spt_values on number> 0 and number <= quantity
where type='P'

NB: The code above uses the master..spt_values table - this is just for demo purposes I suggest you create your own tally table using one of the techniques here.

Answer 2

If you are on SQL Server 2005 or later version, then you can try a recursive CTE instead of a tally table.

;WITH CTE AS
(
    SELECT orderid, 1 item_num, product, quantity
    FROM YourTable
    UNION ALL
    SELECT orderid, item_num+1, product, quantity
    FROM CTE
    WHERE item_num < quantity
)
SELECT *
FROM CTE
OPTION (MAXRECURSION 0)

I'm not on a computer with a database engine where I can test this, so let me know how it goes.