Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to multiply rows in one table based on the value on another table Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 2:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:09 AM
Points: 156, Visits: 661
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

Post #1382334
Posted Thursday, November 8, 2012 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:09 AM
Points: 156, Visits: 661
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:09 AM
Points: 156, Visits: 661
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...
Post #1382339
Posted Thursday, November 8, 2012 2:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 5,078, Visits: 11,861
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




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.
Post #1382342
Posted Thursday, November 8, 2012 2:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:09 AM
Points: 156, Visits: 661
@Phil - thanks, much simpler than my solution :)
Post #1382345
Posted Thursday, November 8, 2012 4:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:50 AM
Points: 2,856, Visits: 5,124
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!"
(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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:09 AM
Points: 156, Visits: 661
Beautiful, thanks :)
Post #1382400
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse