select statement query

[jrwinterburn]

Does anyone if the following is possible:

I have a table with records in that one of the columns ("entries") has an integer. What I want to do is select all records from the table, but if a row has (for example) 37 as the value for "entries", then that row is selected 37 times.

Does this make sense? It's basically for a lotto draw - there are individual entries for each person, but each of those has an integer for the amount of tickets they bought.

Thanks,

Jon

 

[gmmastros]

It's possible. In fact, there are probably several solutions. Here's one.

You will first need a number table in your database. Make sure the number table has MANY times more records than you expect in the entries column of your main table. To build your numbers table...

Create Table Numbers(Number Integer)

Declare @i Integer
Set @i = 1
While @i <= 1000
  Begin
    Insert Into Numbers(Number) Values(@i)
    Set @i = @i + 1
  End

Now, you can use the numbers table to duplicate the rows in your main table.

In this example, I will use a table variable to represent your main table. When you implement this, you will need to replace @Temp with your table name.

[green]-- build the table variable and fill it with data[/green]
Declare @Temp Table (Name VarChar(50), Entries Integer)

Insert Into @Temp Values('gmmastros', 10)
Insert Into @Temp Values('jrwinterburn', 5)

[green]-- replace * with the columns you want to return
-- replace @Temp with your table name[/green]
Select * 
From   @Temp T
       Left Join Numbers 
         On T.Entries >= Numbers.Number

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom