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

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
Asked:
Banthor
  • 2
  • 2
  • 2
  • +1
1 Solution
 
danrosenthalCommented:
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
 
danrosenthalCommented:
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
 
SharathData 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

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
BanthorAuthor 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
 
BanthorAuthor Commented:
Actually CTE's are limited to recursion limit of 100
0
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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