Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2007
    Posts
    13

    Unanswered: how to concatenate rows in db2

    how to concatenate rows in db2 for one column?
    it is required that no temporary tabel variables be used
    Last edited by fatalerror; 11-25-07 at 18:16.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Class assignment ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2007
    Posts
    13
    nope absolutely not i changed the names of the columns to have it understood with an ease

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You need aggregation. Have a look at the XMLAGG function.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2007
    Posts
    13
    the xmlagg function returns result in xml format which is not wanted i tried the aggregate function, i have researched on concat in db2, group_concat in mysql but it doesnt help i implemented this using plsql where i used table variables but here i am restricted from using them as my sysadmin doesnot allow me to do it

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the problem with XML data types?

    Have a look here at the first answer: http://www.thescripts.com/forum/thread542844.html
    Pretty much the same is done there: regular strings are converted to XML, then XMLAGG combines this to a single scalar (XML) value, then you serialize it to a string and finally strip-out the XML tags. Homework done!
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2007
    Posts
    13
    substr(replace(replace(
    xmlserialize(content xmlagg(
    xmlelement(name \"c\", colname) order by colno)
    would do but i have around 24000 such rows to be checked for concatenation of the lists in the table so would iterating around it be fruitful?

  8. #8
    Join Date
    Nov 2007
    Posts
    13
    the xmlagg function returns result in xml format which is not wanted as i will have to save this information in an already existing table and i cannot use a temp table to save these values i tried the aggregate function, i have researched on concat in db2, group_concat in mysql but it doesnt help i implemented this using plsql where i used table variables but here i am restricted from using them as my sysadmin doesnot allow me to do it and also aggregate functions imply to columns in the table

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The number of rows doesn't matter - this is SQL, which is tailored to processing multiple rows. The argument with "xml format" is bogus because you can convert between strings and XML without any problems. So where exactly do you get stuck?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by fatalerror
    CLASS SEX ROLLNUM COURSE-LIST
    1 M 101 CEM
    2 M 101 E
    2 M 102 CEM
    3 M 103 CMP
    4 M 101 C
    4 M 102 CM
    4 M 103 C
    4 F 101 CE
    5 M 101 C
    5 F 101 C
    If there really is a (relatively low) upper bound on the number of values in the last column (4 here, it seems from the doc), this can be achieved with a self-join (4x the same table, left outer join) and a "<" join condition.
    (Will be far more expensive than the XMLAGGR solution, though.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by fatalerror
    ... i will have to save this information in an already existing table and i cannot use a temp table to save these values ...
    Given the first statement I quoted here, I don't understand at all why you initially said that no "variables, temp tables, global temp tables, or common table expressions (with clause)" are an option. Usually, such restrictions are imposed only in class exercises. Practical problems often allow you to search for all possible solutions and then picking the best approach - instead of reducing the solution space right away as you have done. I guess you will understand that all who responded to your question have this impression.

    Could you explain to us why recursive queries (common table expressions) are not allowed, for instance? You could implement the CTE inside a table function and then use this table function in an INSERT statement - no materialized temp tables will be required (but DB2 uses a barrier to first build an intermediate result in memory, which is essentially a temp table) and the recursive CTE can be used in a straight-forward manner to do the concatenation. Likewise, XMLAGG does the aggregation of a bunch of XML values. The links provided above show how to use that function for your purposes. You can even throw this into a subselect and do whatever you want. You can process multiple nested groups on millions of rows if you like. Therefore, I think it is fair to raise the question what the problems with XMLAGG are. What have you tried? How were it failing/not producing the correct results?

    ... and also aggregate functions imply to columns in the table
    I didn't understand this part. Could you please explain what you mean by it? If you mean by "columns in the table" a column in a base table, you are mistaken. You can do aggregation on anything in SQL because - at the end of the day - everything is a table in SQL. Even a simple statement like this produces a table:
    Code:
    VALUES (123)
    This is a table with one row and one column, so you could aggregate on it (or do whatever other features SQL provides, for example:
    Code:
    SELECT MIN(v)
    FROM TABLE ( VALUES (123) ) AS t(v)
    The only important part is that you properly define your groups on which an aggregation is to be applied.

    p.s: I don't intend to bash MySQL now, but the way how some DBMS adhere to the SQL standard and implement relational concepts is sometimes not the best. Therefore, drawing conclusions for one DBMS based on experiences gathered on MySQL is often not a good idea.
    Last edited by stolze; 11-24-07 at 06:14.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Nov 2007
    Posts
    13
    all these are specifications for the problem

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Then have fun with your exercise.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Nov 2007
    Posts
    13
    I am basically a java developer and i am asked to write this sp for a db2 database and my dba restricted me in using temptables for this sp anywaz thanks for your suggestions

  15. #15
    Join Date
    May 2003
    Posts
    113
    with the latest XML functions, it is doable with XMLAGG and XMLQUERY functions, such as(run on db2/zos):


    CREATE TABLE TT
    (COL1 VARCHAR(10));

    INSERT INTO TT VALUES('welcome');
    INSERT INTO TT VALUES('udb');
    INSERT INTO TT VALUES('db2base');

    SELECT
    XMLSERIALIZE(
    XMLQUERY('/fn:data(COL1)' PASSING
    XMLDOCUMENT(
    XMLAGG(XMLELEMENT(NAME "COL1", COL1))) )
    AS CLOB(50) EXCLUDING XMLDECLARATION)
    FROM TT;


    +----------------------------------------------------+
    | |
    +----------------------------------------------------+
    1_| welcome udb db2base |
    +----------------------------------------------------+


    DB2/LUW should support similar functions, but you need V9.

Posting Permissions

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