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

  • 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

  • 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.

  • 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...

  • 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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • @Phil - thanks, much simpler than my solution 🙂

  • 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]

  • Beautiful, thanks 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply