Results 1 to 5 of 5

012004, 12:06 #1Registered User
 Join Date
 Jan 2004
 Posts
 2
Unanswered: Expand table rows based on two column ranges
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

012004, 12:26 #2Moderator.
 Join Date
 Sep 2002
 Location
 UK
 Posts
 5,171
Provided Answers: 1Re: Expand table rows based on two column ranges
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;Tony Andrews
http://tinyurl.com/tonyandrews

012004, 14:17 #3Registered User
 Join Date
 Jan 2004
 Posts
 2
Re: Expand table rows based on two column ranges
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?
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;

012004, 14:50 #4SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
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?
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

012104, 05:40 #5Registered User
 Join Date
 Aug 2001
 Posts
 66
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>Padderz
SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline