I have a table of product records which includes a field with the qty on hand. I need to query that table returning as many rows for each product record as the value in the qty on hand field.
What is the best way to 'explode' the rows based upon the value in the qty field?
I have pasted the code from my attempt to use a table-value function; that does not work.
CREATE FUNCTION [dbo].[rowsFromValue](@qty int)
RETURNS @retArray TABLE (idx smallint Primary Key)
DECLARE @idx smallint
IF @qty = null
SET @qty = 0
SET @idx = 0
WHILE @qty > 0
SET @idx = @idx + 1
INSERT @retArray (idx)
SET @qty = @qty - 1
CREATE TABLE [PRODUCT](
[UPC] [varchar](14) NULL,
[ONHAND_QTY] [int] NULL)
FROM PRODUCT AS P
CROSS APPLY [Cognos].[dbo].[rowsFromValue](ONHAND_QTY) AS R