November 8, 2012 at 2:05 am
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
November 8, 2012 at 2:09 am
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.
November 8, 2012 at 2:19 am
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...
November 8, 2012 at 2:24 am
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 8, 2012 at 2:34 am
@Phil - thanks, much simpler than my solution 🙂
November 8, 2012 at 4:00 am
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)
November 8, 2012 at 4:18 am
Beautiful, thanks 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply