Results 1 to 9 of 9
  1. #1
    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    19
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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

    can't help you any further unless you show your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    Thank you for all your help!!

  8. #8
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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