InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 02:23:12 GMT20http://www.sqlservercentral.com/Forums/Topic1382334-338-1.aspxBeautiful, thanks :)Thu, 08 Nov 2012 04:18:19 GMT_simon_http://www.sqlservercentral.com/Forums/Topic1382334-338-1.aspxI think that one could be slightly faster:[code="sql"]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)[/code]Thu, 08 Nov 2012 04:00:17 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1382334-338-1.aspx@Phil - thanks, much simpler than my solution :)Thu, 08 Nov 2012 02:34:21 GMT_simon_http://www.sqlservercentral.com/Forums/Topic1382334-338-1.aspxHere's another possibility:[code="sql"]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[/code]Thu, 08 Nov 2012 02:24:20 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1382334-338-1.aspxGot 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...):[code]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[/code]Then my table function:[code]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[/code]Test:[code]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)[/code]Edit - talking to my self, sorry, if this is agains some rules you can delete the topic...Thu, 08 Nov 2012 02:19:12 GMT_simon_http://www.sqlservercentral.com/Forums/Topic1382334-338-1.aspxHm... 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.Thu, 08 Nov 2012 02:09:15 GMT_simon_http://www.sqlservercentral.com/Forums/Topic1382334-338-1.aspxData:[code]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[/code]Is it possible to get those results (see below) from my @t1 table with a single select without loops?[code]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[/code]Thu, 08 Nov 2012 02:05:45 GMT_simon_