• Status: Solved
• Priority: Medium
• Security: Public
• Views: 241

# 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?
0
Banthor
• 2
• 2
• 2
• +1
1 Solution

Commented:
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
``````
0

Commented:
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
``````
0

Data EngineerCommented:
try this.
``````insert manifest
select t1.rowid,t1.Itemid,t1.qty
from OrderedITems t1
cross join master..spt_values t2
where t2.type = 'P' and t2.number < t1.qty
``````
0

Author Commented:
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
0

Author Commented:
Actually CTE's are limited to recursion limit of 100
0

Commented:
>>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.
0

Commented:
Actually even that is not true, if you are brave you can set it to 0.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 2
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.