?
Solved

Expanding QTYxItem to row of unique ITEMs

Posted on 2011-04-22
7
Medium Priority
?
239 Views
Last Modified: 2012-05-11
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
Comment
Question by:Banthor
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Expert Comment

by:danrosenthal
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

Open in new window

0
 
LVL 15

Expert Comment

by:danrosenthal
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

Open in new window

0
 
LVL 41

Accepted Solution

by:
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

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Author Closing Comment

by:Banthor
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

by:Banthor
ID: 35451431
Actually CTE's are limited to recursion limit of 100
0
 
LVL 75

Expert Comment

by:Anthony Perkins
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

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question