Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    1

    Unanswered: Database table design

    Hi,

    I have a table in DB2 UDB with data that looks like this:
    col1 col2 col3
    1 |500 |15
    1 |1500 |12
    1 |1000 |10
    2 |300 |12
    2 |800 |10


    I want to create a new table in DB2 UDB that takes this data and displays it as follows:
    col1 col2 col3
    1 |500,1500,1000 |15,12,10
    2 |300,800 |12,10

    Does anyone know how I can do this in DB2 UDB?
    Last edited by sandrak; 02-13-02 at 17:56.

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Database table design

    As this is an old posting, this information is for those who have a similar question in the future.

    One way to approach this problem is to create a function(s) which contains a FOR loop which would concatenate the data found for the value of col1 passed to the function. Example:
    CREATE FUNCTION testf(id integer)
    LANGUAGE SQL
    NOT DETERMINISTIC
    NO EXTERNAL ACTION
    READS SQL DATA
    RETURNS VARCHAR(1000)
    BEGIN ATOMIC
    FOR X AS
    SELECT col2 FROM t
    WHERE col1 = id
    DO
    SET v_value = col2 || ', ';
    END FOR;
    RETURN SUBSTR(v_value, 1, LENGTH(v_value) - 2);
    END

    To use it:
    INSERT INTO test2 (col1, col2, col3)
    SELECT col1, testf(col1), testf2(col1) FROM t


    Originally posted by sandrak
    Hi,

    I have a table in DB2 UDB with data that looks like this:
    col1 col2 col3
    1 |500 |15
    1 |1500 |12
    1 |1000 |10
    2 |300 |12
    2 |800 |10


    I want to create a new table in DB2 UDB that takes this data and displays it as follows:
    col1 col2 col3
    1 |500,1500,1000 |15,12,10
    2 |300,800 |12,10

    Does anyone know how I can do this in DB2 UDB?

Posting Permissions

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