• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 861
  • Last Modified:

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.

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

Open in new window

0
pmdadmin
Asked:
pmdadmin
1 Solution
 
jderaCommented:
I think you want to move this to the SQL Server zone.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now