Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to multiply rows in one table based on the value on another table Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 8, 2012 2:05 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 5:33 AM Points: 169, Visits: 773
 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`
Post #1382334
 Posted Thursday, November 8, 2012 2:09 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 5:33 AM Points: 169, Visits: 773
 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.
Post #1382336
 Posted Thursday, November 8, 2012 2:19 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 5:33 AM Points: 169, Visits: 773
 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...
Post #1382339
 Posted Thursday, November 8, 2012 2:24 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:37 PM Points: 7,981, Visits: 19,117
 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.When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.If your posting includes some T-SQL code, please surround the code with IFCode formatting tags. It helps readability a lot.
Post #1382342
 Posted Thursday, November 8, 2012 2:34 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 5:33 AM Points: 169, Visits: 773
 @Phil - thanks, much simpler than my solution :)
Post #1382345
 Posted Thursday, November 8, 2012 4:00 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, December 6, 2016 8:21 AM Points: 2,934, Visits: 5,478
 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!"(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help
Post #1382381
 Posted Thursday, November 8, 2012 4:18 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 5:33 AM Points: 169, Visits: 773
 Beautiful, thanks :)
Post #1382400

 Permissions