How to multiply rows in one table based on the value on another table

• _simon_

  SSCrazy

  Points: 2329

  More actions

  November 8, 2012 at 2:05 am

  #393558

  Data:

  declare @t1 table (Id int)

  insert into @t1 (Id)

  select 1 union

  select 2 union

  select 3

  declare @t2 table (Id int, X int)

  insert into @t2 (Id, X)

  select 1, 10 union

  select 2, 5 union

  select 3, 0

  Is it possible to get those results (see below) from my @t1 table with a single select without loops?

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 2 union all

  select 2 union all

  select 2 union all

  select 2 union all

  select 2

• _simon_

  SSCrazy

  Points: 2329

  More actions

  November 8, 2012 at 2:09 am

  #1557053

  Hm... I could create table valued function which gets an id from first table and the number of repeats from second table and performs a cross join... Will try it out now.

• _simon_

  SSCrazy

  Points: 2329

  More actions

  November 8, 2012 at 2:19 am

  #1557054

  Got it

  First we need a table function which returns all integers between x and y (the function is stolen from somewhere, don't remember where...):

  create function [dbo].[f_table_numbers] (@st bigint, @end bigint)

  returns table

  as

  return

  with

  a as (select convert(bigint,0) as n union all select 0),

  b as (select 0 as n from a as a cross join a as b),

  c as (select 0 as n from b as a cross join b as b),

  d as (select 0 as n from c as a cross join c as b),

  e as (select 0 as n from d as a cross join d as b),

  f as (select 0 as n from e as a cross join e as b),

  nums as (select row_number() over (order by (select 1)) as num from f as a cross join f as b)

  select @st + num - 1 as num

  from nums

  where num <= @end-@st+1

  Then my table function:

  create function Temp (@Id int, @N int)

  returns @t table (Id int)

  as

  begin

  insert into @t (Id)

  select x.Id

  from (select @Id Id) x cross join (select Num from dbo.f_table_numbers(1, @N)) y

  return

  end

  Test:

  declare @t1 table (Id int)

  insert into @t1 (Id)

  select 1 union select 2 union select 3

  declare @t2 table (Id int, X int)

  insert into @t2 (Id, X)

  select 1, 10 union

  select 2, 5 union

  select 3, 0

  ;with temp as (

  select t1.Id, t2.X

  from @t1 t1 join @t2 t2 on (t1.Id = t2.Id)

  )

  select t1.Id

  from temp t1 cross apply dbo.Temp(t1.Id, t1.X)

  Edit - talking to my self, sorry, if this is agains some rules you can delete the topic...

• Phil Parkin

  SSC Guru

  Points: 247169

  More actions

  November 8, 2012 at 2:24 am

  #1557056

  Here's another possibility:

  declare @t1 table (Id int)

  insert into @t1 (Id)

  select 1 union

  select 2 union

  select 3

  declare @t2 table (Id int, X int)

  insert into @t2 (Id, X)

  select 1, 10 union

  select 2, 5 union

  select 3, 0;

  with t3 as (

  select id, x, row_number() over (partition by id order by id) ro from @t2

  cross join sys.all_columns

  )

  select id, x from t3 where ro <= x

  ---

• _simon_

  SSCrazy

  Points: 2329

  More actions

  November 8, 2012 at 2:34 am

  #1557058

  @Phil - thanks, much simpler than my solution

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  November 8, 2012 at 4:00 am

  #1557084

  I think that one could be slightly faster:

  select t1.id

  from @t1 t1

  inner join @t2 t2 on t2.Id = t1.Id and t2.X > 0

  cross apply (select top (t2.X) 1 from sys.columns) n(n)

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• _simon_

  SSCrazy

  Points: 2329

  More actions

  November 8, 2012 at 4:18 am

  #1557101

  Beautiful, thanks