Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Unanswered: create single field from many records

    Okay, I'm struggling here. I'm sure this can be done but I can't see the wood for the trees...

    The table contains data like...

    1 A
    1 B
    1 C
    2 A
    2 B
    2 C

    And I want to convert that to...

    1 A,B,C
    2 A,B,C

    I can't do this in a procedure or a function, it has to be a standard SQL statement. Any ideas?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can check out this site for a possible answer:

    http://searchdatabase.techtarget.com...285649,00.html

    I just did this (like yesterday) by writing a SQL function to perform this task. If you want the code let me know.

    Andy

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Thanks Andy.

    Unfortunately, I will probably have a large (and varying) number of elements to concatanate, so that won't work for me.

    After much sweat and several tears, I came up with this though (I've included some example data if you want to try it)...

    CREATE TABLE STOCK_OPTIONS
    (
    STK_ID SMALLINT
    , OPT_DESC CHAR(1)
    );

    INSERT INTO STOCK_OPTIONS
    VALUES (1,'A'),(1,'B'),(1,'C'),(2,'A'),(2,'B'),(2,'C')
    ;

    WITH INITIAL_STOCK_LIST ( STK_ID, OPT_DESC ) AS
    (
    SELECT DISTINCT STK_ID, RTRIM(OPT_DESC)
    FROM STOCK_OPTIONS
    )
    -- The following temp table holds a count to be used to determine
    -- which record will be the final record in the later, recursive select.
    , STOCK_COUNTER (STK_ID, OPT_COUNT) AS
    (
    SELECT STK_ID, COUNT(*)
    FROM INITIAL_STOCK_LIST
    GROUP BY STK_ID
    )
    -- The following temp table adds an incremental index (opt_id)
    , BASE_STOCK_LIST ( OPT_ID, STK_ID, OPT_DESC ) AS
    (
    SELECT COUNT(*), A.STK_ID, A.OPT_DESC
    FROM INITIAL_STOCK_LIST A, INITIAL_STOCK_LIST B
    -- This is the join used to attain an incremental index starting from 1
    -- for the options
    WHERE A.OPT_DESC >= B.OPT_DESC
    AND A.STK_ID = B.STK_ID
    GROUP BY A.STK_ID, A.OPT_DESC
    )
    , RECURSIVE_STOCK_LIST ( OPT_ID, STK_ID, OPT_DESC, COUNTER ) AS
    (
    SELECT OPT_ID, STK_ID, CAST(OPT_DESC AS VARCHAR(2000)), 1
    FROM BASE_STOCK_LIST

    UNION ALL

    SELECT A.OPT_ID, A.STK_ID, A.OPT_DESC || ', '|| B.OPT_DESC, A.COUNTER + 1
    FROM BASE_STOCK_LIST B, RECURSIVE_STOCK_LIST A
    WHERE A.STK_ID = B.STK_ID
    -- Only need to create one new row on each recursion
    -- Use the counter to append the 'next' opt_desc element to the most
    -- complete 'full' description ONLY
    AND B.OPT_ID = A.OPT_ID + COUNTER
    AND B.OPT_ID = COUNTER + 1

    )
    SELECT A.STK_ID, OPT_DESC
    FROM RECURSIVE_STOCK_LIST A
    , STOCK_COUNTER B
    WHERE A.STK_ID = B.STK_ID
    AND A.COUNTER = B.OPT_COUNT
    ;

    I'm still working on the performance ;-)

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Damian

    IMHO, you can create BASE_STOCK_LIST using RANK rather than using a join ...

    BASE_STOCK_LIST ( STK_ID, OPT_DESC,opt_id ) AS
    (
    select stk_id,opt_desc,
    rank() over (partition by stk_id
    order by opt_desc
    )
    from initial_stock_list
    )

    This will make it more readable and efficient

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Originally posted by sathyaram_s


    BASE_STOCK_LIST ( STK_ID, OPT_DESC,opt_id ) AS
    (
    select stk_id,opt_desc,
    rank() over (partition by stk_id
    order by opt_desc
    )
    from initial_stock_list
    )
    Brilliant!

    While I'm at it... I've based all the query elements of the statement on the INITIAL_STOCK_LIST select, which is essentially a temporary table. Would I maybe see an improvement in performance if I used the original table data, which is indexed, rather than the temporary table data? It is possible/likely that the temporary data would be written to disk rather than be held in memory as the volume could be quite large.

    That said, how could I determine what volume of data would require that a temporary table be written to disk?

    Thanks, Damian

Posting Permissions

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