Creating Multiple Rows Repeating Info from one Row in a table
-
I have order transactions that appear as one row in a table with a quantity field...if the quantity field contains the quantity 4, I need to create four rows repeating the order number...if it contains a quantity of 10 I need to create 10 rows....any ideas on the best way to create a temp table to have the repeating rows? I would assume you use a cursor, but don't know where to begin. Can anyone provide an example?
We need to take 1 order line with a quantity of greater than one and repeat it for as many time as the quantity field's value is. This will be used for printing shipping lables. If an order line is for 4 of ItemA, we need to print 4 shipping lables for ItemA. I assume the best way to do that is by creating multiple rows in a temp table with a box 1 of 4, 2 of 4 in each row of the table and printing from there.
Thanks!
-
try this
Create table #orders(orderid int, quantity int)
insert #orders
select 1,3 union all
select 2,1 union all
select 3,4 union all
select 4,2
GO
Create procedure orderproc as
begin
declare @oid int, @cnt int,@i int
Create table #temp(orderid int, quantity int)
declare cur cursor for
select orderid,quantity as cnt from #orders
open cur
fetch next from cur into @oid,@cnt
while @@fetch_status=0
begin
set @i=@cnt
while @i>0
begin
insert into #temp values(@oid,@cnt)
set @i=@i-1
end
fetch next from cur into @oid,@cnt
end
close cur
deallocate cur
select * from #temp
end
-
Much faster to use a triangular join on a numbers (tally) table...
Insert into .... Select ...
from products inner join dbo.Tally on Quantity >= Tally.NumberID
-
I've read about Numbers table in SSC and how it can be used to avoid cursors.
Here it is.
Create table #Numbers(Num int)
Declare @ID int
set nocount on
set @ID = 1
while (@ID <= 100000)
Begin
insert into #Numbers
select @ID
set @ID = @ID + 1
End
set nocount off
--select * from #Numbers
Create table #orders(orderid int, quantity int)
insert #orders
select 1,3 union all
select 2,1 union all
select 3,4 union all
select 4,2
select * from #orders INNER JOIN #Numbers ON #orders.quantity >= #Numbers.Num
-
I absolutely agree with using a tally table as Remi and Sreejith have... I just can't bring myself to create a tool that helps avoid cursors and loops by using a loop to create it
--===== Create and populate the Tally table on the fly (= 30+ years of dates, too)
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just for the records..
4860 rows in dbo.SysColums of 2000
11237 in sys.Syscolums of 2005
So the cross join might be overkill in 2005 unless you realllllllllllly need 126 270 169 rows in the tally table
. -
"TOP 11000" must prevent server from death.
_____________
Code for TallyGenerator -
Tried running it without top... Stopped the query after 10 minutes, no dice
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply