Results 1 to 6 of 6

Thread: Query question

  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Query question

    My business system vendor, in their great wisdom, has constructed a table in a difficult to use format. The table hold comments that are input into a text box on a web form, or through a Green Screen application. The input box allows for multiple lines so they store each line of the input in a seperate record with an ID number in the first column, a line number in the second column, and one line of the text field in the third column. The ID number then repeats and the line number increments for each line that was input.

    I need to find a way, preferably using SQL to view this data in a more concise form with a single record containing the entire comment with each record containing the ID number in the first column and all of the lines of text concatenated together in the second column.

    I have seached in vain for a method to do this but it is difficult to describe to a search engine what I am trying to do. Does anyone have any ideas?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You want to concatenate strings. Search a bit here or on your favorite search engine - you will find lots of material. Basically, you can use recursive queries or the XMLAGG function (DB2-specific) to perform the aggregation.
    Last edited by stolze; 01-16-08 at 03:55.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want what mysql's GROUP_CONCAT function does trivially

    you neglected to mention which DBMS you're using, which is often important when an ANSI SQL solution may be difficult
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2008
    Posts
    2
    Sorry, My DBMS is DB2 on OS/400 V5R4

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to DB2 forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by jclark01
    I need to find a way, preferably using SQL to view this data in a more concise form with a single record ...
    Is an SQL stored procedure an option?
    Then you just need to open a cursor (returning the rows in the right order), concat the rows into a local variable, and return that variable.
    --_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
  •