How to turn single rows into multiples.... 1

[Peager]

I need a good set based solution to run a select statement that generates :

131234 2
134321 5
154234 3

I need:
131234
131234
134321
134321
134321
134321
134321
154234
154234
154234

I can think of several convoluted ways of getting here but there has to be a slick set-based solution.

Anybody?

Much thanks in advance.

Paul

 

[markros]

Try

declare @t table (iNumber int, Occurrences int)
insert into @t 
select
131234,    2
union all
select
134321,    5
union all select
     154234,    3
     
select iNumber, Occurrences, number
from @t T inner join numbers on number between 1 and  Occurrences

assuming that you have Numbers table in the database. If you don't, check a script of creating this table in this blog
Passing ranges to SP

PluralSight Learning Library

 

[Peager]

Markos,

What is 'numbers' (on the inner join in the last line?


Msg 208, Level 16, State 1, Line 11
Invalid object name 'numbers'.

 

[Peager]

Markos,

Never mind, I got it.... It's a table of integers.....

Works GREAT!