Expand table rows based on two column ranges

pcm23 · #1 (**permalink**) 01-20-04, 12:06

Hi,
I've got a table I'm working with in SQL (through a SAS interface) with the following structure: There is a primary key and for each primary key there are two columns that together constitute a range of values for the primary key. The first column is the start value, the second is the edn value. Here's a quick example:

prim_key startvalue endvalue
1 1023 1026
2 1028 1033
3 1036 1038

What I want to do is expand the table so that each value associated with a primary key is contained on its own row as shown in the table below. I've tried worked with sequences, but this doesn't appear to be the way to go. Does anyone have any suggestions on how I might accomplish this task?

prim_key value
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038

andrewst · #2 (**permalink**) 01-20-04, 12:26

What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.

You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:

select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;

You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:

select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;

pcm23 · #3 (**permalink**) 01-20-04, 14:17

I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?

Quote:

Originally posted by andrewst
What you need is a table with all the possible VALUEs in it, i.e. from the min(start_value) to the max(end_value). Or from 1 to max(end_value) would do.

You could create such a table, or a table of integers from 1 to some big enough number, and then join to it like this:

select t.prim_key, v.value
from my_table t, values_table v
where v.value between t.start_value and t.end_value;

You may also be able to "fake" such a table, e.g. in Oracle you could fake it like this:

select t.prim_key, v.value
from my_table t, (select rownum as value from all_objects) v
where v.value between t.start_value and t.end_value;

r937 · #4 (**permalink**) 01-20-04, 14:50

Quote:

I'm having trouble creating the table. The number of inserts to this table is over 500,000. How would I do this usign loops?

please, no loops, okay?

you can generate a million integers from just the integers 0 though 9 like this:

Code:

create table integers (i integer); 
insert into integers (i) values (0); 
insert into integers (i) values (1); 
insert into integers (i) values (2); 
insert into integers (i) values (3); 
insert into integers (i) values (4); 
insert into integers (i) values (5); 
insert into integers (i) values (6); 
insert into integers (i) values (7); 
insert into integers (i) values (8); 
insert into integers (i) values (9); 

select 100000*hk.i
     +  10000*tk.i
     +   1000*k.i
     +    100*h.i
     +     10*t.i
     +        u.i as num
  from integers hk
cross
  join integers tk
cross
  join integers k
cross
  join integers h
cross
  join integers t
cross
  join integers u

and then left join to your tables as desired

padderz · #5 (**permalink**) 01-21-04, 05:40

Pipelined function may come in handy here...

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> CREATE TYPE numbers_table AS TABLE OF NUMBER (10);
2 /

Type created.

SQL> CREATE OR REPLACE FUNCTION expand (
2 start_value IN NUMBER,
3 end_value IN NUMBER)
4 RETURN numbers_table PIPELINED
5 AS
6 BEGIN
7 FOR i IN start_value..end_value LOOP
8 pipe row (i);
9 END LOOP;
10 RETURN;
11 END;
12 /

Function created.

SQL> CREATE TABLE table_name (
2 prim_key NUMBER,
3 startvalue NUMBER,
4 endvalue NUMBER);

Table created.

SQL> INSERT INTO table_name VALUES (1, 1023, 1026);

1 row created.

SQL> INSERT INTO table_name VALUES (2, 1028, 1033);

1 row created.

SQL> INSERT INTO table_name VALUES (3, 1036, 1038);

1 row created.

SQL> SELECT prim_key, column_value
2 FROM table_name, TABLE (expand (startvalue, endvalue));

PRIM_KEY COLUMN_VALUE
---------- ------------
1 1023
1 1024
1 1025
1 1026
2 1028
2 1029
2 1030
2 1031
2 1032
2 1033
3 1036
3 1037
3 1038

13 rows selected.

SQL>

Internet Services

Web Development

Digital Marketing

Consumer Tech