October 28, 2010 at 3:57 pm
Here's an odd requirement.
The source table (simplified to protect the guilty):
CREATE TABLE MultiRowTest
(id INT identity(1,1),
MyValue VARCHAR(10),
Qty INT)
Here's some data for the table:
INSERT INTO MultiRowTest
SELECT 'abc',1 UNION ALL
SELECT 'def', 1 UNION ALL
SELECT 'zzz', 2 UNION ALL
SELECT 'hello', 4 UNION ALL
SELECT 'bye',1
I want to query this table and return a number of rows for each source row equal to the value of the Qty column. So, expected results from the above would be:
ID MyValue Qty
---- -------- ------
1 abc 1
2 def 1
3 zzz 2
3 zzz 2
4 hello 4
4 hello 4
4 hello 4
4 hello 4
1 bye 1
What I've done so far is create a Table valued UDF that takes the quantity as a parameter and returns a dummy table with that number of rows. I then CROSS APPLY against that table. Like this:
CREATE FUNCTION fnMultiRows
(@ReturnRows int )
RETURNS
@Table_Var TABLE (C1 INT )
AS
BEGIN
DECLARE @i int
set @i = 1
WHILE @i <= @ReturnRows
BEGIN
insert into @Table_Var VALUES (@i)
set @i = @i + 1
END
RETURN
END
and then...
SELECT ID, MyValue, Qty
FROM MulitRowTest2 CROSS APPLY dbo.fnMultiRows(Qty) X
This gives me the results I want, but the UDF is probably pretty inefficient, especially in light of the fact that in 80% of the cases, the value of Qty will be 1. In those cases, it will 'dummy up' that 1 row table needlessly.
My question for all of you is 1) is there a better way to get the multi-row results without resorting to a cross apply on a table valued function and if not, 2) is there a more efficient way to write the UDF?
Thanks in advance.
Rob Schripsema
Propack, Inc.
October 28, 2010 at 8:38 pm
All you need is a tally table and then the following works:
select
mrt.id,
mrt.MyValue,
mrt.Qty
from
MultiRowTest mrt
cross join dbo.Tally t
where
t.N <= mrt.Qty
October 28, 2010 at 8:51 pm
Lynn,
That's brilliant. And just what I needed.
Thanks!
Rob Schripsema
Propack, Inc.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply