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

 Author Message _simon_ Ten Centuries Group: General Forum Members Points: 1239 Visits: 780 Data:`declare @t1 table (Id int)insert into @t1 (Id)select 1 union select 2 union select 3declare @t2 table (Id int, X int)insert into @t2 (Id, X)select 1, 10 unionselect 2, 5 unionselect 3, 0`Is it possible to get those results (see below) from my @t1 table with a single select without loops?`select 1 union allselect 1 union allselect 1 union allselect 1 union allselect 1 union allselect 1 union allselect 1 union allselect 1 union allselect 1 union allselect 1 union allselect 2 union allselect 2 union allselect 2 union allselect 2 union allselect 2` _simon_ Ten Centuries Group: General Forum Members Points: 1239 Visits: 780 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_ Ten Centuries Group: General Forum Members Points: 1239 Visits: 780 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 tableasreturnwith 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 numfrom numswhere num <= @end-@st+1`Then my table function:`create function Temp (@Id int, @N int) returns @t table (Id int)asbegin insert into @t (Id) select x.Id from (select @Id Id) x cross join (select Num from dbo.f_table_numbers(1, @N)) y returnend`Test:`declare @t1 table (Id int)insert into @t1 (Id)select 1 union select 2 union select 3declare @t2 table (Id int, X int)insert into @t2 (Id, X)select 1, 10 unionselect 2, 5 unionselect 3, 0;with temp as ( select t1.Id, t2.X from @t1 t1 join @t2 t2 on (t1.Id = t2.Id))select t1.Idfrom 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 Group: General Forum Members Points: 128952 Visits: 22583 Here's another possibility:`declare @t1 table (Id int)insert into @t1 (Id)select 1 union select 2 union select 3declare @t2 table (Id int, X int)insert into @t2 (Id, X)select 1, 10 unionselect 2, 5 unionselect 3, 0;with t3 as (select id, x, row_number() over (partition by id order by id) ro from @t2cross join sys.all_columns)select id, x from t3 where ro <= x` Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot. _simon_ Ten Centuries Group: General Forum Members Points: 1239 Visits: 780 @Phil - thanks, much simpler than my solution Eugene Elutin SSC-Dedicated Group: General Forum Members Points: 32102 Visits: 5478 I think that one could be slightly faster:`select t1.idfrom @t1 t1inner join @t2 t2 on t2.Id = t1.Id and t2.X > 0cross 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 _simon_ Ten Centuries Group: General Forum Members Points: 1239 Visits: 780 Beautiful, thanks