Query to return N rows where N is a field of the row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to return N rows where N is a field of the row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-20 : 09:13:09

Jason writes "The problem:
I have a table (Order) which contains a shipping address (Order.Address) and an order quantity (Order.Qty).
I want a query to output the address N times where N is equal to the quantity.
eg. with rows in Order = (2,foo) (4,bar) (3,baz)
I want the query to return:

foo
foo
bar
bar
bar
bar
baz
baz

So far, the closest I've come to a solution is using a join with a table of numbers in a range (1..50) and then using a where clause to get only those order rows which are >= the number in the number range table.
ie.
SELECT * from Order,Numbers
WHERE Order.Qty >= Numbers.Number
ORDER BY Order.Address

This, however, is inefficient and limited to the size of the number table. The bigger I make that, the less efficient the join is.

So... Is there a way of doing this without resorting to such hacky tricks????

Regards,
Jason Williams"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-20 : 09:34:24

I don't consider this to be a hack, it's probably the most efficient way to do it. It would work better if you index the Qty and Number columns. The thing that's really killing it is the ORDER BY clause...again, indexing the Address column could improve the performance dramatically, or losing the ORDER BY altogether.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-20 : 11:59:56

Since no columns from the Numbers table appear in the ORDER BY, it should sort Orders before the join. I definitely agree, though, that Numbers.Number must be indexed. In the normal course of things, it should be the pk of Numbers.

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-20 : 14:35:07

You could do it this way:

declare @i integer
declare @max integer

declare @temptable table(qty integer, nm varchar(3))

set @max = (select max(qty) from test)
set @i = 1

while @i <=@max
begin

insert into @temptable
select qty, nm
from test
where qty >=@i

set @i = @i +1
end

Whether that is any more efficient than your way I don't know ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources