# Thread: Create row from number range

1. Registered User
Join Date
Jan 2007
Posts
19

## Unanswered: Create row from number range

Greetings:

I would like to create a row for each number range in a table and add its associated fields with it.

Code:
```Source Table has:

BEG_NUM         END_NUM         PRINTER      USER     DATE
150                   153               P01            Ed         6-1-07

I would like to convert that to a table which consists of:
Number     Printer       User        Date
150           P01           Ed           6-1-07
151           P01           Ed           6-1-07
152           P01           Ed           6-1-07
153           P01           Ed           6-1-07```
How can I create this using SQL? The source table has lots of rows.

Thanks,
Eric

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```select min(Number) as BEG_NUM
, max(Number) as END_NUM
, Printer     as PRINTER
, User        as USER
, Date        as DATE
from Source
group
by Printer
, User
, Date```

3. Registered User
Join Date
Jan 2007
Posts
19
r937,

Your solution combines the rows. I want to make a row for each number between the beginning and ending number, so if beginning is 150 and ending is 153 i would have 4 rows listed from that one row. 150,151,152,153.

My goal is to have one row for each series of numbers.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
aaargh, i misunderstood, i got your tables backwards, didn't i

you will need an integers table:
Code:
```create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)```
then you can generate your range of numbers like this:
Code:
```select BEG_NUM + i as Number
, PRINTER     as Printer
, USER        as User
, DATE        as Date
from integers
inner
join Source
on BEG_NUM + i <= END_NUM```

5. Registered User
Join Date
Jan 2007
Posts
19
When I do the 2nd part, I get invalid column name for all except i because I am selecting fields from integer table that don't exist.

I tried adding the correct table names and selecting from both tables and then joining but it doesn't work. It says tables have the same exposed names.

When I tried another way, it gave me 10 rows for each number, which wasnt what i want either

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
i tested my query and it works

probably your table is actually different from what you posted, or you made an error in your query

7. Registered User
Join Date
Jan 2007
Posts
19
I apologize, Its working now.

Thank you for all your help!!

8. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
The "filling holes in a table" problem is a well-known "difficult" one in relational databases.
If your database system supports it, you should use recursive SQL for the "integers table" part of the story:
Code:
```WITH integers(i) AS (VALUES (0)
UNION ALL
SELECT i+1 FROM integers
WHERE i < 9)
SELECT ... <Rudy's query>```
Now this query is much more flexible in case there are bigger holes than width 10: just replace the "9" by, say, "99".
Or better yet, replace it by (SELECT MAX(End_Num-Beg_Num) FROM Source) or something like that, so that the query becomes completely unparametrized and will work on any table with any numeric column pair.
(Or with a date pair, for that matter.)
Last edited by Peter.Vanroose; 06-08-07 at 13:40.

9. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
nice trick, peter, and i mean that as a sincere compliment

it is a true "hack" and that is a good thing

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•