Create row from number range

data1025 · #1 (**permalink**) 06-06-07, 17:49

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

r937 · #2 (**permalink**) 06-06-07, 17:54

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

data1025 · #3 (**permalink**) 06-06-07, 21:55

r937,

Thank you for your reply.

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.

r937 · #4 (**permalink**) 06-06-07, 22:29

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

data1025 · #5 (**permalink**) 06-07-07, 07:16

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

r937 · #6 (**permalink**) 06-07-07, 07:36

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

can't help you any further unless you show your query

data1025 · #7 (**permalink**) 06-07-07, 07:57

I apologize, Its working now.

Thank you for all your help!!

Peter.Vanroose · #8 (**permalink**) 06-08-07, 12:35

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.)

r937 · #9 (**permalink**) 06-08-07, 12:38

nice trick, peter, and i mean that as a sincere compliment

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

Internet Services

Web Development

Digital Marketing

Consumer Tech