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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy