Results 1 to 7 of 7

Thread: DB2 Looping

  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: DB2 Looping

    I have a table with the following columns

    EmpId,Location,Name
    1,10,FL
    1,11,TX
    1,24,GA
    2,10,FL
    2,20,AL
    3,10,FL


    The O/P i want is

    Empid,Location

    1, 10:FL,11:TX,24:GA
    2, 10:FL,20:AL
    3, 10:FL

    I need to concatenate the values of location and name per employee id into one string value

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS? There are different ways to do this, it depends on the DB2 version and OS. This question gets asked a lot. You can search this forum for the different answers.

    Andy

  3. #3
    Join Date
    Feb 2004
    Posts
    19
    DB2 Version is 9070400 on Linux

  4. #4
    Join Date
    Aug 2008
    Posts
    147
    Is this just for presentation layer - or is there a requirement to UPDATE\INSERT?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2004
    Posts
    19
    It worked..thanks a lot..
    For some tables where the concat length is greater than 4K,listagg fails.Is there any way of dealing it or should we loop it through a procedure?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see manuals.

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

    Code:
    Table 1. Result data type of LISTAGG function
    +------------------------------------+---------------------------------+
    |   Data type of string-expression   |       Data type of result       | 
    +------------------------------------+---------------------------------+
    | CHAR(n) or VARCHAR(n)              | VARCHAR( MAX(4000, n))          |
    +------------------------------------+---------------------------------+
    | GRAPHIC(n or VARGRAPHIC(n)         | VARGRAPHIC( MAX(2000, n))       |
    +------------------------------------+---------------------------------+
    CAST the values to be concatenated as longer VARCHAR.

Posting Permissions

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