asked on

Expanding QTYxItem to row of unique ITEMs

I have a OrderedITems table (rowid [int],Itemid [int],qty[smallint])
I need to insert into the manifest one row for each item as expressed by Qty

So if OrderedItems contains QTY 6 of Item 4
My select needs to return 6 rows of ITEMID 4

Does someone have a single select statement solution?

danrosenthal

If it has to be a single select statement you are a little limited. Here is one way to do it that would work for up to a qty of 10...

SELECT o.rowID, o.itemID FROM OrderedITems o
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=2
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=3
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=4
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=5
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=6
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=7
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=8
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=9
UNION ALL SELECT o.rowID, o.itemID FROM OrderedITems o WHERE qty >=10
ORDER BY rowID, itemID

Open in new window

danrosenthal

Here is an example using a temp table. It will work for unlimited qty...

create table #temp (
	rowID INT
	, itemID INT
)

DECLARE @loop INT
SET @loop = 1

WHILE @loop <= (SELECT MAX(qty) FROM OrderedITems)
BEGIN
INSERT INTO #temp SELECT rowID, itemID FROM OrderedITems WHERE qty >= @loop
SET @loop = @loop + 1
END

SELECT * FROM #temp ORDER BY rowID, itemID

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Banthor

ASKER

Cross Join is not something I use a lot, but this work great. I should look into using a CTE so that I am not limited to the value of spt_values

Banthor

ASKER

Actually CTE's are limited to recursion limit of 100

Anthony Perkins

>>Actually CTE's are limited to recursion limit of 100 <<
I suspect you mean:
Actually CTE's are limited to a default recursion limit of 100

The actual max limit is 32,767. See MAXRECURSION hint.

Anthony Perkins

Actually even that is not true, if you are brave you can set it to 0.