SQL problem to solve...

15 Sep 2006 12:41 AM

SammyBar

Hi all,

I'm facing the following problem. I should return the records from a table
by using a store procedure. But each record should be repeated a number of
time. The number of time the record should be repeated in the output is
specified in a column in the table.

For example, for the following table:
Declare @tbTest Table (
Name Varchar(20),
Repeat Int
)
Insert Into @tbTest Values ('A', 2)
Insert Into @tbTest Values ('B', 3)

The stored procedure output should be:
A
A
B
B
B

My solution involves use of cursors to iterate over the table to reinsert
each record the amount of times it is specified in the Repeat field, but I
wonder is it a more elegant solution without using cursors. I'd like some
kind of JOINs usage that solve the problem in a simple Select query.
Is it possible?

Thanks in advance
Sammy

15 Sep 2006 12:55 AM

SQL Ken

You can do a double while loop
the first one is for the number of record, the second is for the
'Repeat'
value and inside the loop insert the name into a temp table.

finally, you select from that temp table.

ken

SammyBar wrote:
Show quoteHide quote

> Hi all,
>
> I'm facing the following problem. I should return the records from a table
> by using a store procedure. But each record should be repeated a number of
> time. The number of time the record should be repeated in the output is
> specified in a column in the table.
>
> For example, for the following table:
> Declare @tbTest Table (
> Name Varchar(20),
> Repeat Int
> )
> Insert Into @tbTest Values ('A', 2)
> Insert Into @tbTest Values ('B', 3)
>
> The stored procedure output should be:
> A
> A
> B
> B
> B
>
> My solution involves use of cursors to iterate over the table to reinsert
> each record the amount of times it is specified in the Repeat field, but I
> wonder is it a more elegant solution without using cursors. I'd like some
> kind of JOINs usage that solve the problem in a simple Select query.
> Is it possible?
>
> Thanks in advance
> Sammy

15 Sep 2006 1:02 AM

Chris Lim

SQL Ken wrote:

> You can do a double while loop
> the first one is for the number of record, the second is for the
> 'Repeat'
> value and inside the loop insert the name into a temp table.
>
> finally, you select from that temp table.

Exactly how is this better than using cursors, which is what the OP was
asking for?

15 Sep 2006 1:01 AM

Chris Lim

SammyBar wrote:

> My solution involves use of cursors to iterate over the table to reinsert
> each record the amount of times it is specified in the Repeat field, but I
> wonder is it a more elegant solution without using cursors. I'd like some
> kind of JOINs usage that solve the problem in a simple Select query.
> Is it possible?

If you have a Numbers table, then you could join onto that and avoid
using a cursor.

Chris