Solved

# Expanding QTYxItem to row of unique ITEMs

Posted on 2011-04-22
Medium Priority
239 Views
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
Question by:Banthor
• 2
• 2
• 2
• +1

LVL 15

Expert Comment

ID: 35450975
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

LVL 15

Expert Comment

ID: 35450997
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

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35451254
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

LVL 10

Author Closing Comment

ID: 35451409
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

LVL 10

Author Comment

ID: 35451431
Actually CTE's are limited to recursion limit of 100
0

LVL 75

Expert Comment

ID: 35451444
>>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

LVL 75

Expert Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this ā¦
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month16 days, 10 hours left to enroll