Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Smile Unanswered: Nested Select Statement

    Hi everyone,

    I need a little help with concatenating multiple rows from a related table.

    I have TABLE1 and TABLE2. Both tables have a field called IDNUM. TABLE2 also has a field called TEXTFLD. I want to concatenate all of the

    I want to concatenate all of the rows returned from a nested select statement similar to the select statement below. This result will return anywhere from 0 to 100 rows. Can someone help me with the proper syntax ?

    SELECT TEXTFLD WHERE TABLE2.IDUM = TABLE1IDNUM

    My query should return the following columns:

    TABLE1.IDNUM, TABLE1.PRODUCTDESC, concatenated TEXTFLDS AS DETAILED DESC

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This is an FAQ. Please search this forum, or internets in general, for "pivot query" or "rows to columns". Depending on your DB2 version, you can use XMLAGG() or LISTAGG().

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by bgreer2710 View Post
    [...] My query should return the following columns:
    [...] concatenated TEXTFLDS AS DETAILED DESC
    This can be achieved with recursive SQL using CTEs; see e.g. the following threads in this forum: query, special transpose and merge of data - dBforums, http://www.dbforums.com/showthread.php?t=918402 and http://www.dbforums.com/showthread.php?t=1200060
    Note that you need to specify the concatenation order in some way or the other, e.g. through a third column. (Aggregate SUM, COUNT etc. are order-indifferent, CONCAT isn't.)
    Your "DESC" indeed hints at a reversely alphabetic sort, but syntactically you can of course not specify it there.

    See also my presentation at IDUG in 2007 on CTE's (especially pages 44-50): http://www.abis.be/resources/present...1106ctedb2.pdf

    As noted by n_i, there is also the workaround through XML (if you use DB2 9 or higher), but that round-trip to XML and back is counter-intuitive and hence maybe slightly less readable/maintainable/portable.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I like the LISTAGG() function. It is close to XMLAGG in terms of functionality, but it doesn't involve the XML-specific tags. Whether you can use it depends on your specific DB2 product and version, though.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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