Results 1 to 5 of 5
  1. #1
    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: 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;

  3. #3
    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;

  4. #4
    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?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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

Posting Permissions

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