Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25

    Unanswered: [ASA 9.2] How to bring multiple rows from a single row

    Hello,

    I've a product quantity table that look like

    Code:
    ID	QTY
    1	5
    2	3
    3	2
    I want to retrieve product id duplicated such as

    Code:
    ID
    1
    1
    1
    1
    1
    2
    2
    2
    3
    3
    this should be done with the value stored in the QTY column.

    I don't know how to do this in SQL, anyone have an idea?

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I don’t use ASA but can’t imagine why you want to do this.
    Rather repeat the display of the row in your app then having the overhead to retrieve duplicate info from the database.

    But if you insist, create a table with sequential numbers and join to it.
    Code:
    create table prodqty (id int,qty int)
    insert into prodqty values(1,5)
    insert into prodqty values(2,3)
    insert into prodqty values(3,2)
    
    create table numbers (seqno int)
    insert into numbers values(1)
    insert into numbers values(2)
    insert into numbers values(3)
    insert into numbers values(4)
    insert into numbers values(5)
    insert into numbers values(6)
    
    select a.id from prodqty a, numbers b
    where a.qty>=b.seqno
    
    drop table prodqty 
    drop table numbers

  3. #3
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25
    Thanks a bunch for your reply,

    for further information, my question was issued after a design issue in existing application code that I prefer not modify, and thought it would be simplier to solve it by just replacing the problematic stored procedure.

    As your solution is stand-in if I replace the sequence table with a row_number function call on an existing accordingly sized table, it seems to fit neatly.

    Also sorry for not using the simple sql statement for my question, would do it now.

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    An alternative without intermediate or temp tables is:
    - set rowcount 1
    - use a loop and traverse the product table one row at a time
    - for each row, use a second loop and print (or select, as required) an incremented value until the limit of product.qty is reached

    It is filthy and slow, but it will work

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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