Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2010
    Posts
    9

    Question Unanswered: Concatenate values from same column of different rows

    Hi,

    My table contains the following data: Basically a meaningful sentence is broken into pieces and placed into a column in separate rows as shown below.

    db2 "select * from temptab where id=10"

    ID NUM STRING
    ----------- ----------- ---------
    10 7 a
    10 8 pen
    10 5 is
    10 1 This

    4 record(s) selected.

    I could read the sentence using the following query : i.e. by using order by clause

    db2 "select * from temptab where id=10 order by num"

    ID NUM STRING
    ----------- ----------- ------------
    10 1 This
    10 5 is
    10 7 a
    10 8 pen

    4 record(s) selected.


    How do I get this data as a single row? In other words, How do I concatenate the STRING column data of different rows for the same ID and ordered by NUM?

    Thanks a lot for the response..

    Let me know if anything is unclear

    Regards,
    Pratap

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This question (or similar ones) gets asked about once a month on this forum. Do a search on XMLAGG, you should find your answer.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use LISTAGG on DB2 9.7 Fix Pack 4 or later for LUW.

    Fix Pack 4 also contains the following enhancements:

    •A new aggregate function, LISTAGG, has been added.
    The LISTAGG function aggregates a set of string elements into one string by concatenating the strings.
    Optionally, a separator string can be provided which is inserted between contiguous input strings.
    For more information, see LISTAGG aggregate function.

    •You can use pattern expressions in a LIKE predicate which are based on other columns. ...
    ...


    ...
    Fix pack summary - IBM DB2 9.7 for Linux, UNIX, and Windows

    LISTAGG - IBM DB2 9.7 for Linux, UNIX, and Windows

  4. #4
    Join Date
    Nov 2003
    Location
    Am Ende der Welt
    Posts
    12

    Concatenate values from same column of different rows

    Or you can also use a cursor to read all the rows and concatenate the values in a variable.

    César

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Using cursor is not recommended, if some other ways possible.

    Because, it would make the program longer/complicated and performance degradation.

    Some threads in this forum discussed improvement of bad performance of cursor,
    but significant improvement were not returned even after many suggestions/recommendations/discussions.

  6. #6
    Join Date
    Apr 2012
    Posts
    5
    you can solve by using CASE WHEN logic as below:

    SELECT MAX(STR1), MAX(STR2), MAX(STR3), MAX(STR4) FROM
    (
    SELECT MAX(CASE WHEN NUM = 1 THEN STRING ELSE NULL END) AS STR1
    ,MAX(CASE WHEN NUM = 5 THEN STRING ELSE NULL END) AS STR2
    ,MAX(CASE WHEN NUM = 7 THEN STRING ELSE NULL END) AS STR3
    ,MAX(CASE WHEN NUM = 8 THEN STRING ELSE NULL END) AS STR4
    FROM TEMPTAB
    GROUPBY ID,NUM
    HAVING ID = 10);

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by delta403 View Post
    you can solve by using CASE WHEN logic as below:

    SELECT MAX(STR1), MAX(STR2), MAX(STR3), MAX(STR4) FROM
    (
    SELECT MAX(CASE WHEN NUM = 1 THEN STRING ELSE NULL END) AS STR1
    ,MAX(CASE WHEN NUM = 5 THEN STRING ELSE NULL END) AS STR2
    ,MAX(CASE WHEN NUM = 7 THEN STRING ELSE NULL END) AS STR3
    ,MAX(CASE WHEN NUM = 8 THEN STRING ELSE NULL END) AS STR4
    FROM TEMPTAB
    GROUPBY ID,NUM
    HAVING ID = 10);
    How does this help the OP? First, the data is not being concatenated like the OP wants. Second, you are hard coding the values to order the columns, what if they are different each time? And third, you are only allowing for 4 items, what if there are more?

    Andy

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by ARWinner View Post
    How does this help the OP? First, the data is not being concatenated like the OP wants. Second, you are hard coding the values to order the columns, what if they are different each time? And third, you are only allowing for 4 items, what if there are more?

    Andy
    And fourth, NUM in "GROUP BY ID, NUM" is unnecessary.
    If NUM was removed from grouping column, subquery itself would return same as the result of delta403's query.
    And , "HAVING ID = 10" should be replaced by "WHERE ID = 10".
    If "WHERE ID = 10" was used, GROUP BY clause can be removed.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    On the contrary,
    if all of Andy's three issues were denied, MAX(CASE ...END) solution might make sense.

    Reverse of Andy's three issues:
    (1) the result data are in seperate columns.
    (2) values of order column are known(then hardcoded).
    (3) (maximum) number of columns is fixed.

    See PIVOT in http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

  10. #10
    Join Date
    Apr 2012
    Posts
    5
    There should be chances of having more records with the different num values; which can be solve by using the CASE WHEN logic in sequence like:

    CASE WHEN NUM = 1 THEN STRING ELSE NULL END
    CASE WHEN NUM = 2 THEN STRING ELSE NULL END
    :
    :
    CASE WHEN NUM = N THEN STRING ELSE NULL END

    And at last just GROUP BY with out having clause.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You didn't read(or not understand) Andy's issue, especialy...
    Second, you are hard coding the values to order the columns, what if they are different each time?
    "the values to order the columns"(i.e. NUM) might be 1, 10, 100, 1000, 10000, 100000, ...
    Last edited by tonkuma; 04-24-12 at 12:22.

  12. #12
    Join Date
    Oct 2008
    Posts
    1
    Does everything have to be done in DB2? If you're in a Unix, you could do the following:

    db2 -x "select string from temptab where id=10 order by num" | xargs

  13. #13
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    if you aren't at least at fixpak 4 on 9.7 then
    select substr(xmlserialize(xmlagg(xmltext(concat(', ',equip_id))) as varchar(1024)),3) from equipment ;

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by pratapd View Post
    ID NUM STRING
    ----------- ----------- ------------
    10 1 This
    10 5 is
    10 7 a
    10 8 pen
    Also recursive SQL can help you out here:
    Code:
    WITH aux(auxid,auxnum,concat_string,n) AS
    (SELECT id,num,CAST(rtrim(string) AS varchar(2000)),1
       FROM temptab t
      WHERE num = (SELECT MIN(num) FROM temptab WHERE id=t.id)
     UNION ALL
     SELECT auxid,(SELECT MIN(num) FROM temptab
                 WHERE id = aux.auxid AND num > aux.auxnum),
            concat_string||' '||(SELECT rtrim(string) FROM temptab
                      WHERE id = aux.auxid AND num > aux.auxnum
                      ORDER BY num FETCH FIRST ROW ONLY),
            n+1
       FROM aux
      WHERE auxnum < (SELECT MAX(num) FROM temptab WHERE id=aux.auxid)
        AND n < 10000 -- to avoid infinite loop warning
    )
    SELECT auxid AS id,concat_string FROM aux a
     WHERE auxnum = (SELECT max(auxnum) FROM aux WHERE auxid = a.auxid)
    Explanation:
    - In step one (the part before the UNION ALL), just the first word of each sentence is collected (with its id and its num) into an aux table (or actually CTE);
    - Then, recursively, the next word is appended to those rows. In the end, the aux table contains something like:
    ID NUM STRING
    ----------- ----------- ------------
    10 1 This
    10 5 This is
    10 7 This is a
    10 8 This is a pen

    Finally, only show the rows with the highest num value, for each id.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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