Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Multiple values | seperated

    Hello all,

    Is this possible in oracle via a simple query, well i have a query that return multiple values as its output but is it possible to make the output as a single row with all the values concatenated with | symbol. is it possible without writing a oracle function.

    value1|value2|value3

    something like this.

    Thanks

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hello,
    the best way is to use LISTAGG aggregation function (available from 11gR2).
    If you are not in 11gR2, use some technique described in this link: http://www.oracle-base.com/articles/...Techniques.php except WM_CONCAT, which separates values with comma (and is unsupported by Oracle anyway).

  3. #3
    Join Date
    Sep 2007
    Posts
    56
    Thanks but I am using Oracle 10g

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    If so, why did you not post it? Anyway, just follow in reading my post further:
    Quote Originally Posted by flyboy View Post
    If you are not in 11gR2, use some technique described in this link: http://www.oracle-base.com/articles/...Techniques.php except WM_CONCAT, which separates values with comma (and is unsupported by Oracle anyway).
    and follow it. The choice is yours.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure whether this is what you are looking for, but - SET might help.
    Code:
    SQL> set colsep '|'
    SQL> select * from dept;
    
        DEPTNO|DNAME         |LOC
    ----------|--------------|-------------
            10|ACCOUNTING    |NEW YORK
            20|RESEARCH      |DALLAS
            30|SALES         |CHICAGO
            40|OPERATIONS    |BOSTON
    
    SQL>

  6. #6
    Join Date
    Sep 2007
    Posts
    56
    this is what i am looking for wm_concat(fieldname) this is a inbuilt function in oracle.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by donraja_ht View Post
    this is what i am looking for wm_concat(fieldname) this is a inbuilt function in oracle.
    To be precise, this function is internal (= unsupported) in WMSYS schema (so not available when Oracle installed without it). Additionally, it separates the values with commas (as I stated before). You may REPLACE them with pipes, but the result will be incorrect when column values will contain commas. Use, whatever you want, in case of difficulties keep in mind that you were warned.
    Some other discussion about WM_CONCAT: http://asktom.oracle.com/pls/apex/f?...23200346634568

Posting Permissions

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