pmdadmin
asked on
table-valued function explode qty to rows
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.
thank you.
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.
thank you.
CREATE FUNCTION [dbo].[rowsFromValue](@qty int)
RETURNS @retArray TABLE (idx smallint Primary Key)
AS
BEGIN
DECLARE @idx smallint
IF @qty = null
BEGIN
SET @qty = 0
END
SET @idx = 0
WHILE @qty > 0
BEGIN
SET @idx = @idx + 1
INSERT @retArray (idx)
VALUES (@idx)
SET @qty = @qty - 1
END
RETURN
END
GO
CREATE TABLE [PRODUCT](
[UPC] [varchar](14) NULL,
[ONHAND_QTY] [int] NULL)
SELECT
UPC
,PM_TITLE
,IDX
FROM PRODUCT AS P
CROSS APPLY [Cognos].[dbo].[rowsFromValue](ONHAND_QTY) AS R
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.