Return multiple rows based on value in column?

• Rob Schripsema

  SSCertifiable

  Points: 7469

  More actions

  October 28, 2010 at 3:57 pm

  #228568

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 28, 2010 at 8:38 pm

  #1243963

  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

• Rob Schripsema

  SSCertifiable

  Points: 7469

  More actions

  October 28, 2010 at 8:51 pm

  #1243965

  Lynn,

  That's brilliant. And just what I needed.

  Thanks!

  Rob Schripsema
  Propack, Inc.